How can I make my index / match work in VBA?
I am trying to create a macro that uses Index / match functions to match and retrieve data from one sheet to another. I did it in Excel and it works fine. However, the reports are "dynamic" (resizing) so I need the last line of my code and dynamic. The following is what I did. I NOW get an error like "mismatch" (I underline "now" as every time I find a solution for one error, a popup appears).
Dim prosheet As Worksheet
Dim prosheet2 As Worksheet
Set prosheet2 = ThisWorkbook.Sheets("shipstation")
Set prosheet = ThisWorkbook.Sheets("macrotestfb")
lr1 = prosheet.Cells(Rows.Count, 1).End(xlUp).Row
lr2 = prosheet2.Cells(Rows.Count, 1).End(xlUp).Row
lrship = prosheet.Cells(Rows.Count, 10).End(xlUp).Row
lrindex = prosheet2.Cells(Rows.Column, 14).End(xlUp).Row
'CALCULATE SHIPPING COST
For x = prosheet.range("j6") To lrship
x = Application.WorksheetFunction.Index(prosheet2.range("a1:n" & lrindex), Application.WorksheetFunction.Match(prosheet.range("a6:a" & lr1), prosheet2.range("a1:a" & lr2), 0), prosheet2.range("f2"))
Next x
source to share
Matching in a non-arrayed form only likes one value in the first criterion, not a range.
Also WorksheetFunction.Match will throw an error that will stop the code if no match is found.
I like to pull the match into my line and check the error.
I also adjusted your For statement.
There is no harm in whole column lookups, so I got rid of a few of your last row lookups as they are unnecessary.
Dim prosheet As Worksheet
Dim prosheet2 As Worksheet
Dim x As Long
Dim t As Long
Set prosheet2 = ThisWorkbook.Sheets("shipstation")
Set prosheet = ThisWorkbook.Sheets("macrotestfb")
lrship = prosheet.Cells(Rows.Count, 1).End(xlUp).Row
'CALCULATE SHIPPING COST
For x = 6 To lrship
t = 0
On Error Resume Next
t = Application.WorksheetFunction.Match(prosheet.Range("A" & x), prosheet2.Range("A:A"), 0)
On Error GoTo 0
If t > 0 Then
prosheet.Cells(x, "J").Value = prosheet2.Range("F"&t)
Else
prosheet.Cells(x, "J").Value = "Item does not Exist"
End If
Next x
source to share
Note:
Instead of the combination Index
/ Match
that you can use in a worksheet, you can use Application.Match
in VBA. Something like that:
Sub GetMatch
Dim indexRng As Range, matchRng as Range
Set indexRng = ThisWorkbook.Worksheets("Sheet1").Range("A1:A10")
Set matchRng = ThisWorkbook.Worksheets("Sheet1").Range("B1:B10")
debug.print indexRng.Cells(Application.Match("something",matchRng,0)).Value
End Sub
source to share