Matching 2 Criterias that change in a loop

[JonesZoid]

Hi All

I currently have a piece of code that matches 1 criteria and updates my tracker.
I would like now to introduce a second criteria to the match statement.

My current code is:

Sub UpdateTracker()

Dim Mast As Worksheet
Dim HOP As Worksheet

Set Mast = ThisWorkbook.Worksheets(4)
Set HOP = ThisWorkbook.Worksheets(1)

Application.ScreenUpdating = False
For x = 2 To 30
If Mast.Range("D" & x) = "" Then
Exit Sub
Else
i = Mast.Range("D" & x).Value
m = Application.Match(i, Worksheets("Tracker").Range("B:B"), 0) 
If IsError(m) = True Then
   MsgBox "The ID " & m & " Is Not Found"
   Exit Sub
End If
End If

I have tried the following as the second criteria is a fixed value on this occasion.

Sub UpdateTracker()

Dim Mast As Worksheet
Dim HOP As Worksheet

Set Mast = ThisWorkbook.Worksheets(4)
Set HOP = ThisWorkbook.Worksheets(1)

Application.ScreenUpdating = False
For x = 2 To 30
If Mast.Range("D" & x) = "" Then
Exit Sub
Else
i = Mast.Range("D" & x).Value
j = "Comm"
m = Application.Match(i, Worksheets("Tracker").Range("B:B"), 0) + Application.Match(j, Worksheets("Tracker").Range("K:K"), 0)
If IsError(m) = True Then
   MsgBox "The ID " & m & " Is Not Found"
   Exit Sub
End If
End If

It does retrieve the information, but it doesn't put it in the right row that matches i.

I have searched forums, and could only find, fixed value examples.

Thanks In Advance.

 

[JLGWhiz]

Hi All

I currently have a piece of code that matches 1 criteria and updates my tracker.
I would like now to introduce a second criteria to the match statement.

My current code is:

Sub UpdateTracker()

Dim Mast As Worksheet
Dim HOP As Worksheet

Set Mast = ThisWorkbook.Worksheets(4)
Set HOP = ThisWorkbook.Worksheets(1)

Application.ScreenUpdating = False
For x = 2 To 30
If Mast.Range("D" & x) = "" Then
Exit Sub
Else
i = Mast.Range("D" & x).Value
m = Application.Match(i, Worksheets("Tracker").Range("B:B"), 0) 
If IsError(m) = True Then
   MsgBox "The ID " & m & " Is Not Found"
   Exit Sub
End If
End If

I have tried the following as the second criteria is a fixed value on this occasion.

Sub UpdateTracker()

Dim Mast As Worksheet
Dim HOP As Worksheet

Set Mast = ThisWorkbook.Worksheets(4)
Set HOP = ThisWorkbook.Worksheets(1)

Application.ScreenUpdating = False
For x = 2 To 30
If Mast.Range("D" & x) = "" Then
Exit Sub
Else
i = Mast.Range("D" & x).Value
j = "Comm"
m = Application.Match(i, Worksheets("Tracker").Range("B:B"), 0) + Application.Match(j, Worksheets("Tracker").Range("K:K"), 0)
If IsError(m) = True Then
   MsgBox "The ID " & m & " Is Not Found"
   Exit Sub
End If
End If

It does retrieve the information, but it doesn't put it in the right row that matches i.

I have searched forums, and could only find, fixed value examples.

Thanks In Advance.

You need to post the part of the code that shows where the m value is posted to the sheet.

 

[JonesZoid]

Here is the full code, I have modified the sheet names to match the variables set at the beginning, as I had noticed, I wasn't using them.
This is the code only showing the one criteria, which finds the NEID, I need to include column K, which has the value of "Comm".

Sub UpdateTracker()

Dim Mast As Worksheet
Dim HOP As Worksheet

Set Mast = ThisWorkbook.Worksheets(4)
Set HOP = ThisWorkbook.Worksheets(1)

Application.ScreenUpdating = False

For x = 2 To 30
If Mast.Range("D" & x) = "" Then
Exit Sub
Else
i = Mast.Range("D" & x).Value
m = Application.Match(i, HOP.Range("B:B"), 0)
If IsError(m) = True Then
   MsgBox "The NEID " & m & " Is Not Found"
   Exit Sub
End If
End If
Mast.Activate
Range("K" & x).Select
Selection.Copy
HOP.Activate
Range("AB" & m).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
Mast.Activate
Range("T" & x).Select
Selection.Copy
HOP.Activate
Range("Z" & m).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
Mast.Activate
Range("U" & x).Select
Selection.Copy
HOP.Activate
Range("V" & m).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
HOP.Activate
With Range("P" & m)
.Value = Date
.NumberFormat = "dd/mm/yyyy"
End With
        
HOP.Activate
With Range("Q" & m)
.Value = Date
.NumberFormat = "dd/mm/yyyy"
End With
        
HOP.Activate
With Range("S" & m)
.Value = Date
.NumberFormat = "dd/mm/yyyy"
End With
         
HOP.Activate
With Range("T" & m)
.Value = Date
.NumberFormat = "dd/mm/yyyy"
End With

HOP.Activate
With Range("U" & m)
.Value = Date
.NumberFormat = "dd/mm/yyyy"
End With

HOP.Activate
With Range("R" & m)
.Value = "Transferred"
.Font.ThemeColor = xlThemeColorAccent4
.Font.TintAndShade = -0.249977111117893
End With

HOP.Activate
With Range("AA" & m)
.Value = "Y"
End With

Next x

Application.ScreenUpdating = True

End Sub

 

[JLGWhiz]

I took the liberty of doing some house cleaning on you code. The Match function returns the relative position of a value in a range of cells as an integer. So in the code below the value of m would be the integer value of where the i variable is located in Column B added to the integer value of where the j variable is located in column K. Or simply put, the sum of the two row numbers where the variable values are located. I am not sure that is what you were expecting. Maybe if you could better explain what your objective is, we could provide better assistance.

Sub UpdateTracker()
Dim Mast As Worksheet, HOP As Worksheet, i As Variant, j As Variant, m As Long
Set Mast = ThisWorkbook.Worksheets(4)
Set HOP = ThisWorkbook.Worksheets(1)
Application.ScreenUpdating = False
    For x = 2 To 30
        If Mast.Range("D" & x) = "" Then
            Exit Sub
        Else
            i = Mast.Range("D" & x).Value
            j = "Comm"
            [COLOR="#0000CD"]m = Application.Match(i, HOP.Range("B:B"), 0) + Application.Match(j, HOP.Range("K:K"), 0)[/COLOR]
            If IsError(m) = True Then
                MsgBox "The NEID " & m & " Is Not Found"
                Exit Sub
            End If
        End If
        With Mast
            .Range("K" & x).Copy
            HOP.Range("AB" & m).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            .Range("T" & x).Copy
            HOP.Range("Z" & m).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            .Range("U" & x).Copy
            HOP.Range("V" & m).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
        End With
        With HOP.Range("P" & m)
            .Value = Date
            .NumberFormat = "dd/mm/yyyy"
        End With
        With HOP.Range("Q" & m)
            .Value = Date
            .NumberFormat = "dd/mm/yyyy"
        End With
        With HOP.Range("S" & m)
            .Value = Date
            .NumberFormat = "dd/mm/yyyy"
        End With
        With HOP.Range("T" & m)
            .Value = Date
            .NumberFormat = "dd/mm/yyyy"
        End With
        With HOP.Range("U" & m)
            .Value = Date
            .NumberFormat = "dd/mm/yyyy"
        End With
        With HOP.Range("R" & m)
            .Value = "Transferred"
            .Font.ThemeColor = xlThemeColorAccent4
            .Font.TintAndShade = -0.249977111117893
        End With
        HOP.Range("AA" & m).Value = "Y"
    Next
Application.ScreenUpdating = True
End Sub

 

[JonesZoid]

Thank You very much for cleaning my code.

m is finding the correct NEiD, and j is Comm, but the data its retrieving, is not copying to the row, where m and j are matching?

Any ideas.

EDIT

In column K - "Comm" can be listed hundreds of times - maybe, this is where the issue sits.

 

[JLGWhiz]

Thank You very much for cleaning my code.

m is finding the correct NEiD, and j is Comm, but the data its retrieving, is not copying to the row, where m and j are matching?

Any ideas.

EDIT

In column K - "Comm" can be listed hundreds of times - maybe, this is where the issue sits.

Please uhderstand that the match function does not return a value for the cell it matches. It returns the row number as an integer. The Match function, as used int the code for j variable would return the row number for the first occurence of "Comm", no matter how many times it is listed. If the first occurence is on row 12 Then it will add 12 to whatever row number the i variable holds and that will be the value of m. For example, j match is row 12, i match is row 22 then m value = 34. So all of your subsequent useage of m as a row number in the latter part of the code is referring to row 34. Is that what you want?

If you are attempting to use two different row numbers to copy and paste in the subsequent code, then you would have to assign a different variable to the "Comm" match and use separate copy and paste actions.