近期帮用户自动抽取数据写的宏。
'WXHWYD
'This macro is for sorting data automatically only for Cummins casher
'Stones create on 2017/6/16
'Sub BankDailyAuto()
Sub AutoBankDaily()On Error GoTo ErrHandle'stop screen-updating for user so program runs more fastApplication.ScreenUpdating = False'total line number of DataSourceDim TotalLineNum As Integer'last index of DataSourceDim LastRowNum As Integer'vender nameDim VendorName As String'descriptionDim Description As String'each row of transaction type in DataSource sheetDim eachRowTranctionType As String'each row of Additional Comments in DataSource sheetDim eachRowAdditionalComments As String'each row of ORDP row indexDim ORDPeachRowIndex As Integer'each row of REMI row indexDim REMIeachRowIndex As Integer'each row of BEMN row indexDim BEMNeachRowIndex As Integer'lenth of each row 's Addition CommentsDim LenOfAdditionComt As Integer'select "DataSource" sheetSheets("DataSource").SelectLastRowNum = 1Do While Range("A" & LastRowNum).Value <> ""LastRowNum = LastRowNum + 1Loop'get total line number of DataSourceTotalLineNum = LastRowNum - 2'judge if user pasted data in DataSource sheetIf TotalLineNum <= 0 ThenMsgBox "please paste data in DataSource Sheet!"Exit SubEnd If'get absolute value at column TRange("T2").SelectActiveCell.FormulaR1C1 = "=ABS(RC[-5])"Selection.AutoFill Destination:=Range("T2:T" & (LastRowNum - 1))'get absolute value at column URange("U2").SelectActiveCell.FormulaR1C1 = "=ABS(RC[-5])"Selection.AutoFill Destination:=Range("U2:U" & (LastRowNum - 1))Range("T2:U" & (LastRowNum - 1)).CopySheets("Result").SelectRange("I3").SelectSelection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _:=False, Transpose:=False'clear temp data column T and column U in DataSource sheetSheets("DataSource").SelectRange("T2:" & "U" & (LastRowNum - 1)).SelectSelection.ClearContents'copy "Post Date" to 'Date' in sheet result from sheet DatasourceRange("S2:" & "S" & (LastRowNum - 1)).CopySheets("Result").SelectRange("A3").PasteSpecial xlPasteValuesApplication.CutCopyMode = FalseSheets("DataSource").Select'traverse datasource for each rowFor DataSourceRowIndex = 1 To TotalLineNum'get column "Mark" value'1.If Transaction Type is TFR+ then insert into Type Mark as 'Collections' in destination sheet.'2.If Transaction Type is TFR- and addition comments contain 'BENM' then insert into Type Mark as 'E-banking' in destination sheet.'3.If Transaction Type is TFR- and addition comments don’t contain 'BENM' then insert into Type Mark as 'Auto-payment' in destination sheet.'Column "W" is Vender Name'Column "X" is DescriptioneachRowTranctionType = Range("M" & (DataSourceRowIndex + 1)).ValueeachRowAdditionalComments = Range("K" & (DataSourceRowIndex + 1)).ValueLenOfAdditionComt = Len(eachRowAdditionalComments)If eachRowTranctionType = "TFR+" Then'get column "Mark" valueRange("V" & (DataSourceRowIndex + 1)).SelectActiveCell.FormulaR1C1 = "Collections"'"ORDP"If JG_ContainString(eachRowAdditionalComments, "ORDP") = "Y" ThenORDPeachRowIndex = InStr(eachRowAdditionalComments, "/ORDP/")'"REMI"If JG_ContainString(eachRowAdditionalComments, "REMI") = "Y" ThenREMIeachRowIndex = InStr(eachRowAdditionalComments, "/REMI/")Range("W" & (DataSourceRowIndex + 1)).SelectActiveCell.FormulaR1C1 = Mid(eachRowAdditionalComments, ORDPeachRowIndex + 6, REMIeachRowIndex - ORDPeachRowIndex - 6)Range("X" & (DataSourceRowIndex + 1)).SelectActiveCell.FormulaR1C1 = Mid(eachRowAdditionalComments, REMIeachRowIndex + 6)ElseRange("W" & (DataSourceRowIndex + 1)).SelectActiveCell.FormulaR1C1 = Mid(eachRowAdditionalComments, ORDPeachRowIndex + 6)End IfElseRange("W" & (DataSourceRowIndex + 1)).SelectActiveCell.FormulaR1C1 = eachRowAdditionalCommentsEnd IfEnd IfIf eachRowTranctionType = "TFR-" ThenIf JG_ContainString(eachRowAdditionalComments, "BENM") = "Y" ThenBENMeachRowIndex = InStr(eachRowAdditionalComments, "/BENM/")'"REMI"If JG_ContainString(eachRowAdditionalComments, "REMI") = "Y" ThenREMIeachRowIndex = InStr(eachRowAdditionalComments, "/REMI/")Range("W" & (DataSourceRowIndex + 1)).SelectActiveCell.FormulaR1C1 = Mid(eachRowAdditionalComments, BENMeachRowIndex + 6, REMIeachRowIndex - BENMeachRowIndex - 6)Range("X" & (DataSourceRowIndex + 1)).SelectActiveCell.FormulaR1C1 = Mid(eachRowAdditionalComments, REMIeachRowIndex + 6)ElseRange("W" & (DataSourceRowIndex + 1)).SelectActiveCell.FormulaR1C1 = Mid(eachRowAdditionalComments, BENMeachRowIndex + 6)End If'get column "Mark" valueRange("V" & (DataSourceRowIndex + 1)).SelectActiveCell.FormulaR1C1 = "E-banking"ElseRange("W" & (DataSourceRowIndex + 1)).SelectActiveCell.FormulaR1C1 = eachRowAdditionalComments'get column "Mark" valueRange("V" & (DataSourceRowIndex + 1)).SelectActiveCell.FormulaR1C1 = "Auto-payment"End IfEnd IfNextRange("V2:V" & (LastRowNum - 1)).CopySheets("Result").SelectRange("C3").SelectSelection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _:=False, Transpose:=FalseSheets("DataSource").SelectRange("W2:W" & (LastRowNum - 1)).CopySheets("Result").SelectRange("F3").SelectSelection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _:=False, Transpose:=FalseSheets("DataSource").SelectRange("X2:X" & (LastRowNum - 1)).CopySheets("Result").SelectRange("G3").SelectSelection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _:=False, Transpose:=False'clear temp data column T and column U in DataSource sheetSheets("DataSource").SelectRange("V2:" & "X" & (LastRowNum - 1)).SelectSelection.ClearContents
ErrHandle:MsgBox "There is unexpected error .Please contact the administrator Stones Zhang ND936"
End SubPublic Function JG_ContainString(SourceString As String, ContainString As String) As StringIf InStr(SourceString, ContainString) <> 0 Then JG_ContainString = "Y" Else JG_ContainString = "N"
End Function