0

I can't get MATCH to work, please help! I keep getting error 438 at the last line... With only one data point after the header defined, the 'row' value becomes 2, and the loc gets set as "A1:A2".

Thanks in advance!

Dim loc As String
Dim row As Integer
Dim value As Integer

'Get current max rows in table
row = Cells.CurrentRegion.Rows.count
'Define the range as being all of A column to end of data
loc = "A1:A" & row

'lookFor is defined previously as some value to search for
value = ActiveSheet.Match(lookFor, ActiveSheet.range(loc), 0)
Community
  • 1
  • 1
user3338579
  • 3
  • 1
  • what is in your `lookFor` variable? – Dmitry Pavliv Feb 21 '14 at 19:18
  • How are you handling if lookFor doesn't exist in the given range? I believe Match will throw an error if lookFor isn't found. You can avoid this by Dimming value as an object rather than an int. This way value will get a #N/A if the lookFor value isn't found. – Ryan G Feb 21 '14 at 19:22

1 Answers1

1

Change ActiveSheet.Match to Application.Match:

value = Application.Match(lookFor, ActiveSheet.Range(loc), 0)

First tip: don't use variables with special VBA names like value and row. Use something like this instead myValue and lastrow.

Second tip: if Match found nothing, it returns error. To handle it, use this code:

Dim myValue As Variant

myValue = ActiveSheet.Match(lookFor, ActiveSheet.Range(loc), 0)

If Not IsError(myValue) Then
    'do sth
End If

Third tip: if you need to find last non empty row in column A, I suggest you to use

Dim lastRow as Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row

instead

row = Cells.CurrentRegion.Rows.count

Note that I declared lastRow as Long. Using Integer for row variable is bad idea, because maximum value of Integer only 32767.

For more information about finding last row see this @SiddharthRout's answer

Community
  • 1
  • 1
Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80