0

When I use Match() in my VBA sub, it returns a 2042 Error every other time. When no error is returned the code works perfectly fine, it is quite strange... Here is what the code looks like:

target.Sheets("Countries").Range("D3:D" & lastRow) = Application.Trim(target.Sheets("Countries").Range("D3:D" & lastRow).Value)
target.Sheets("Countries").Range("D3:D" & lastRow).NumberFormat = "@"
target.Sheets("Countries").Range("T3:T" & lastRow) = Application.Trim(target.Sheets("Countries").Range("T3:T" & lastRow).Value)

For i = 0 To rowz - 1

    pay = Application.Index(target.Sheets("Countries").Range("G3:G" & lastRow), Application.Match(target.Sheets("TEST").Range("D" & 7 + i), target.Sheets("Countries").Range("D3:D" & lastRow), 0))
    fact = Application.Index(target.Sheets("Countries").Range("T3:T" & lastRow), Application.Match(target.Sheets("TEST").Range("D" & 7 + i), target.Sheets("Countries").Range("D3:D" & lastRow), 0))

'rest of code

Next i

I have also noticed that the error only happens on the lookup values that are numbers formatted as text for some reason, but always works on values that stat with a letter.
When error is returned by payit is also retuned by fact, but when it works for the first one it works for both.

Cindy Meister
  • 25,071
  • 21
  • 34
  • 43
plank
  • 13
  • 7
  • https://stackoverflow.com/questions/15526784/why-am-i-getting-error-2042-in-vba-match – Marcucciboy2 Aug 20 '18 at 12:28
  • I had already tried the solutions suggested in that thread but none of it worked unfortunately – plank Aug 20 '18 at 14:24
  • Make sure that the value you're attempting match is of the same data *type* as the values in the lookup range. This is irrespective of NumberFormat. – David Zemens Aug 20 '18 at 14:34

0 Answers0