Kaniguan1969
09-25-2014, 02:34 AM
Hi,
I have this vba code to find records using 1 criterial from workshet to another worksheet. May i ask help from you guys to modify this macro code using multiple criteria. I would like to add Labels and qty. thanks you.
Below is vba code
Sub FilterAndEvaluate()
Dim wsWork As Worksheet
Dim wsSource As Worksheet
Dim r As Long
Dim tracking As String
Dim trxn As String, labels As String
Dim qty As Integer
Set wsWork = ActiveWorkbook.Sheets("Intransit_")
Set wsSource = ActiveWorkbook.Sheets("CoresStatus")
'Remove the existing filter in WorkingFile sheet
If wsWork.AutoFilterMode = True Then wsWork.AutoFilterMode = False
r = wsWork.UsedRange.Rows.Count
'AutoFilter data in column F of WorkingFile sheet
wsWork.UsedRange.AutoFilter Field:=6, Criteria1:="IN-TRANSIT"
'Loop through every row after filtering
For i = 2 To r
If wsWork.Rows(i).Hidden = False Then
'Get Tranking #
tracking = wsWork.Cells(i, 1).Value
labels = wsWork.Cells(i, 2).Value ---add this criteria
qty = wsWork.Cells(i, 3).Value---add this criteria
'Find matched Tracking # in Source sheet
Set rng = wsSource.UsedRange.Find(What:=tracking, LookAt:=xlWhole)
If Not rng Is Nothing Then
trxn = Trim(rng.Offset(0, 8).Value)
'If the System Trxn is Done, set the status to be "RECEIVED"
If trxn = "Done" Then wsWork.Cells(i, 6).Value = "RECEIVED"
End If
End If
Next
wsWork.AutoFilterMode = False
End Sub
I have this vba code to find records using 1 criterial from workshet to another worksheet. May i ask help from you guys to modify this macro code using multiple criteria. I would like to add Labels and qty. thanks you.
Below is vba code
Sub FilterAndEvaluate()
Dim wsWork As Worksheet
Dim wsSource As Worksheet
Dim r As Long
Dim tracking As String
Dim trxn As String, labels As String
Dim qty As Integer
Set wsWork = ActiveWorkbook.Sheets("Intransit_")
Set wsSource = ActiveWorkbook.Sheets("CoresStatus")
'Remove the existing filter in WorkingFile sheet
If wsWork.AutoFilterMode = True Then wsWork.AutoFilterMode = False
r = wsWork.UsedRange.Rows.Count
'AutoFilter data in column F of WorkingFile sheet
wsWork.UsedRange.AutoFilter Field:=6, Criteria1:="IN-TRANSIT"
'Loop through every row after filtering
For i = 2 To r
If wsWork.Rows(i).Hidden = False Then
'Get Tranking #
tracking = wsWork.Cells(i, 1).Value
labels = wsWork.Cells(i, 2).Value ---add this criteria
qty = wsWork.Cells(i, 3).Value---add this criteria
'Find matched Tracking # in Source sheet
Set rng = wsSource.UsedRange.Find(What:=tracking, LookAt:=xlWhole)
If Not rng Is Nothing Then
trxn = Trim(rng.Offset(0, 8).Value)
'If the System Trxn is Done, set the status to be "RECEIVED"
If trxn = "Done" Then wsWork.Cells(i, 6).Value = "RECEIVED"
End If
End If
Next
wsWork.AutoFilterMode = False
End Sub