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
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