標題: [發問] ByVal a As Range~此類語法的用法，不太清楚 [打印本頁] 作者: sayloveme 時間: 2011-12-30 10:15 標題: ByVal a As Range~此類語法的用法，不太清楚
本帖最後由 sayloveme 於 2011-12-30 14:21 編輯
Public Function FmdFatotal(ByVal a As Range, ByVal b As Range, ByVal c As Integer, ByVal pollutant As String) As Double
Dim IRptData As Integer
IRptData = 6
FmdSTotal = 0
Do Until Left(Trim(Sheets("資料").Cells(IRptData, 4).Value), 1) = "本"
If Sheets("資料").Cells(IRptData, 21) > c Then
If Trim(Sheets("資料").Cells(IRptData, 9).Value) = pollutant Then
FmdFatotal = FmdFatotal + Sheets("資料").Cells(IRptData, 20)
End If
End If
IRptData = IRptData + 1
Loop
End Function
Do While 條件 (條件成立 執行迴圈) / Until 條件 (條件成立 不執行迴圈) ***可置於 Do
if 條件=True Then Exit Do '*** 也可中間設立條件 離開迴圈
Loop While 條件 (條件成立 執行迴圈) / Until 條件 (條件成立 不執行迴圈) ***也可置於 Loop作者: sayloveme 時間: 2011-12-30 17:06
'kuo add start 94/5/24
EmptyRowIndex = GetEmptyRowIndex("匯入")
Cells(EmptyRowIndex, 3).Value = "#" & "X_ExpRep" & "#" '
SQLStr = "SELECT DISTINCT X_ExpRep.* "
SQLStr = SQLStr & "FROM X_ExpRep LEFT JOIN X_ExpRepPol ON (X_ExpRep.XERPFTFacNo = X_ExpRepPol.XRPPFTFacNo) AND (X_ExpRep.XERMEREquipNo = X_ExpRepPol.XRPMEREquipNoP) AND (X_ExpRep.XERSDate = X_ExpRepPol.XRPSDate) AND (X_ExpRep.XEROrder = X_ExpRepPol.XRPOrder) AND (X_ExpRep.XERPosition = X_ExpRepPol.XRPPosition) AND (X_ExpRep.XERFlag = X_ExpRepPol.XRPFlag) "
SQLStr = SQLStr & "WHERE (((X_ExpRepPol.XRPCPOName)='硫氧化物' Or (X_ExpRepPol.XRPCPOName)='氮氧化物') AND ((X_ExpRep.XERPFTFacNo)='" & FacNo & "') AND ((Year([XERSDate])) >=" & WestYS1 & ") AND ((Year([XERSDate])) <=" & WestYS2 & "))"
Call GetDataFromDB(lsExpDBFilePathName, SQLStr, EmptyRowIndex + 1, "Yes")
'
-待續作者: sayloveme 時間: 2012-1-2 13:34
EmptyRowIndex = GetEmptyRowIndex("匯入")
Cells(EmptyRowIndex, 3).Value = "#" & "X_ExpRepPol" & "#" '
SQLStr = "SELECT X_ExpRepPol.* FROM X_ExpRepPol "
SQLStr = SQLStr & "WHERE (((X_ExpRepPol.XRPPFTFacNo)='" & FacNo & "') AND ((X_ExpRepPol.XRPCPOName)='硫氧化物') AND ((Year([XRPSDate]))>=" & WestYS1 & ")) OR (((X_ExpRepPol.XRPPFTFacNo)='" & FacNo & "') AND ((X_ExpRepPol.XRPCPOName)='氮氧化物') AND ((Year([XRPSDate]))>=" & WestYS1 & "))"
Call GetDataFromDB(lsExpDBFilePathName, SQLStr, EmptyRowIndex + 1, "Yes")
'
EmptyRowIndex = GetEmptyRowIndex("匯入")
Cells(EmptyRowIndex, 3).Value = "#" & "X_ExpCtl" & "#" '
SQLStr = "SELECT X_ExpCtl.* FROM X_ExpCtl WHERE (((X_ExpCtl.XECPFTFacNo)='" & FacNo & "') AND ((Year([XECSDate]))>=" & WestYS1 & "))"
Call GetDataFromDB(lsExpDBFilePathName, SQLStr, EmptyRowIndex + 1, "Yes")
'
EmptyRowIndex = GetEmptyRowIndex("匯入")
Cells(EmptyRowIndex, 3).Value = "#" & "X_ExpCtlPol" & "#" '
SQLStr = "SELECT X_ExpCtlPol.* FROM X_ExpCtlPol WHERE (((X_ExpCtlPol.XCPPFTFacNo)='" & FacNo & "') AND ((X_ExpCtlPol.XCPCPOName)='硫氧化物') AND ((Year([XCPSDate]))>=" & WestYS1 & ")) OR (((X_ExpCtlPol.XCPPFTFacNo)='" & FacNo & "') AND ((X_ExpCtlPol.XCPCPOName)='氮氧化物') AND ((Year([XCPSDate]))>=" & WestYS1 & "))"
Call GetDataFromDB(lsExpDBFilePathName, SQLStr, EmptyRowIndex + 1, "Yes")
'
EmptyRowIndex = GetEmptyRowIndex("匯入")
Cells(EmptyRowIndex, 3).Value = "#" & "X_ExpRepReq" & "#" '
SQLStr = "SELECT X_ExpRepReq.* "
SQLStr = SQLStr & "FROM X_ExpRep LEFT JOIN X_ExpRepReq ON (X_ExpRep.XERMEREquipNo = X_ExpRepReq.XRRMEREquipNoP) AND (X_ExpRep.XERFlag = X_ExpRepReq.XRRFlag) AND (X_ExpRep.XERPosition = X_ExpRepReq.XRRPosition) AND (X_ExpRep.XEROrder = X_ExpRepReq.XRROrder) AND (X_ExpRep.XERSDate = X_ExpRepReq.XRRSDate) AND (X_ExpRep.XERPFTFacNo = X_ExpRepReq.XRRPFTFacNo) "
SQLStr = SQLStr & "WHERE (((X_ExpRepReq.XRRSn) Is Not Null) AND ((Year([XERSDate]))>=" & WestYS1 & ") AND ((X_ExpRep.XERPFTFacNo)='" & FacNo & "') AND ((X_ExpRepReq.XRRKind)<3)) "
SQLStr = SQLStr & "ORDER BY X_ExpRep.XERMEREquipNo, X_ExpRep.XERSDate, X_ExpRep.XEROrder, X_ExpRepReq.XRRSn"
Call GetDataFromDB(lsExpDBFilePathName, SQLStr, EmptyRowIndex + 1, "Yes")
'
EmptyRowIndex = GetEmptyRowIndex("匯入")
Cells(EmptyRowIndex, 3).Value = "#" & "X_ExpRepAgt" & "#" '
SQLStr = "SELECT X_ExpRepAgt.* "
SQLStr = SQLStr & "FROM X_ExpRep LEFT JOIN X_ExpRepAgt ON (X_ExpRep.XERFlag = X_ExpRepAgt.XRAFlag) AND (X_ExpRep.XERPosition = X_ExpRepAgt.XRAPosition) AND (X_ExpRep.XEROrder = X_ExpRepAgt.XRAOrder) AND (X_ExpRep.XERSDate = X_ExpRepAgt.XRASDate) AND (X_ExpRep.XERMEREquipNo = X_ExpRepAgt.XRAMEREquipNoP) AND (X_ExpRep.XERPFTFacNo = X_ExpRepAgt.XRAPFTFacNo) "
SQLStr = SQLStr & "WHERE (((X_ExpRepAgt.XRASn) Is Not Null) AND ((X_ExpRep.XERPFTFacNo)='" & FacNo & "') AND (Year([XERSDate])>2002)) "
SQLStr = SQLStr & "ORDER BY X_ExpRepAgt.XRASn"
Call GetDataFromDB(lsExpDBFilePathName, SQLStr, EmptyRowIndex + 1, "No")
'
EmptyRowIndex = GetEmptyRowIndex("匯入")
Cells(EmptyRowIndex, 3).Value = "#" & "End" & "#"
'----------------------------------------------------求取P_FactoryStartRowNbr,P_FactoryCount,P_FactoryFieldCount
Call GetIndex("匯入", "P_Factory")
If CLng(YS_Y) <= 95 Then '95年匯入功能
Call GetIndex("匯入", "P_Exp")
Call GetIndex("匯入", "P_Exp_Pipe_0")
Call GetIndex("匯入", "P_Exp_Pipe_1")
Else
Call GetIndex("匯入", "applyusersend")
Call GetIndex("匯入", "chimneyapply")
End If
Call GetIndex("匯入", "X_ExpRep")
Call GetIndex("匯入", "X_ExpRepPol")
Call GetIndex("匯入", "X_ExpCtl")
Call GetIndex("匯入", "X_ExpCtlPol")
Call GetIndex("匯入", "X_ExpRepReq")
Call GetIndex("匯入", "X_ExpRepAgt")
Call GetIndex("匯入", "End")
'
If P_FactoryCount = 0 Then
MsgBox "在P_Factory中,找不到資料,無法繼續執行"
Exit Sub
End If
If P_FactoryCount > 1 Then
MsgBox "在P_Factory中,資料大於1筆,無法繼續執行"
Exit Sub
End If
'配合961季修正
'If P_ExpCount = 0 Then
'MsgBox "在P_Exp中,找不到資料,無法繼續執行"
'Exit Sub
'End If
'If P_Exp_Pipe_0Count = 0 Then
'MsgBox "在申報中,找不到資料,無法繼續執行"
'Exit Sub
'End If
'改成無初審資料也可審查
'If P_Exp_Pipe_1Count = 0 Then
'MsgBox "在初審中,找不到資料,無法繼續執行"
'Exit Sub
'End If
'----------------------------------------------------填入資料
Call Filldata
'----------------------------------------------------
'
ActiveWorkbook.Save '存檔
End Sub
-----------------------------------------------------------------------我是分隔線--------
