Matching 2 Criterias that change in a loop

JonesZoid

New Member
Joined
Dec 11, 2013
Messages
27
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:
Code:
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.

Code:
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.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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:
Code:
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.

Code:
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.
 
Upvote 0

JonesZoid

New Member
Joined
Dec 11, 2013
Messages
27
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".

Code:
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
 
Upvote 0

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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.
Code:
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
 
Last edited:
Upvote 0

JonesZoid

New Member
Joined
Dec 11, 2013
Messages
27
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.
 
Last edited:
Upvote 0

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,187,142
Messages
5,961,806
Members
438,565
Latest member
jameskbw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top