problems with application.match strings and numbers-CodePudding

I have tried to search and find out why or how to fix this problem.

I have a code that compares values between w1 and w2 and then copy a value from w2 to w1. But it only work when the cell in w1 is a string, not when a number is stored as a string. Hope you understand my problem.

Sub UpdateGPL(sheet, cl, ofs)
'sheet = Sheet, cl = column from, ofs = ofset

Dim w1 As Worksheet, w2 As Worksheet
Dim c As Range, FR As Variant

Application.ScreenUpdating = False

Set w1 = Sheets(sheet)
Set w2 = Sheets("GPL")

w1.Select


For Each c In w1.Range("A2", w1.Range("A" & Rows.Count).End(xlUp))
    FR = Application.Match(c, w2.Columns("C"), 0)
    If IsNumeric(FR) Then c.Offset(, ofs).Value = w2.Range(cl & FR).Value
    
Next c

Application.ScreenUpdating = True

End Sub

CodePudding user response：

Please, try in the next way:

For Each c In w1.Range("A2", w1.Range("A" & Rows.Count).End(xlUp))
    FR = Application.Match(c, w2.Columns("C"), 0)
    If isError(FR) then FR = Application.Match(CStr(c.value), w2.Columns("C"), 0)
    If IsNumeric(FR) Then c.Offset(, ofs).Value = w2.Range(cl & FR).Value
    
Next c

Match function compares identic type of variables. If I correctly understood your question, you should convert it as string when use it (also) for such a case.

But if you try matching a string looking as a number in a numbers range, you should use:

  FR = Application.Match(CLng(c.value), w2.Columns("C"), 0)

If the string looking as a number has decimals, or is larger than 2147483647, you can use CDbl instead of CLng...

CodePudding user response：

@faneDuru came with te perfect solution for me.

For Each c In w1.Range("A2", w1.Range("A" & Rows.Count).End(xlUp))
    FR = Application.Match(c, w2.Columns("C"), 0)
    'If IsError(FR) Then FR = Application.Match(CDbl(c.Value), w2.Columns("C"), 0)
    If IsNumeric(c.Value) Then
        If IsError(FR) Then FR = Application.Match(CDbl(c.Value), w2.Columns("C"), 0)
    End If
    If IsNumeric(FR) Then c.Offset(, ofs).Value = w2.Range(cl & FR).Value
Next c