PDA

View Full Version : [SOLVED] Match Error 2042



JeffH
02-12-2020, 02:55 AM
I have SOLVED this issue. The first number in Cell(2,1) that I was trying to MATCH was 77718 which is a Long Integer. I changed CurrentSNumber to Long and everything works.

If am using two worksheets to copy a value from cell(2,2) in one worksheet based on a match of cell(2,1) to range A2 to Last Row in the second worksheet. In trying to find the row# in the second worksheet that matches Cell(2,1) in the first I can't get past the MATCH code without Error 2042. I've tried hard coding a number 5487 instead of the variable CurrentSNumber and that works, but the variable doesn't.
I've also tried changing the variable type of CurrentSNumber to Integer or Long and that doesn't work. Here is the code and a screen shot of the columns in the second worksheet. Any help would be appreciated. I have even tried a variation of this by hardcoding the variable and using cInt. See code variation below which works.



Sub ReplaceFNumberinShelter()

Sheets("Facility_Owners").Select

Dim CurrentFNumber As String
Dim CurrentSNumber As String
Dim RSNumber As Variant
Dim Xyz As Long
Dim Range1 As Range
Dim LastRow As Integer

Sheets("Facility_Owners").Select

CurrentSNumber = Cells(2, 1).Value
CurrentFNumber = Cells(2, 2).Value

Sheets("Shelter").Select

LastRow = Worksheets("Shelter").Cells(Rows.Count, "A").End(xlUp).Row


RSNumber = Application.Match(CurrentSNumber, Range("A2:A" & LastRow), 0)

Debug.Print RSNumber




End Sub


Variation of the Match code:


CurrentSNumber = 5487
RSNumber = Application.Match(CInt(CurrentSNumber), Range("A2:A" & LastRow), 0)




SNumber
FNumber


1331
1000


1936
1000


5487
1000


7427
1000


8062
1000


8063
1000


8105
1000


10023
1000


12761
1000


12869
1000


12895
1000


12938
1000


13029
1000


13046
1000


13149
1000


14615
1000


26560
1000


44333
1000

Andreaa23
07-25-2023, 01:02 AM
I'm glad to hear this bug has been resolved