Match, use variable rathet than value in quotes

[Bruce_Tjosvold]

Hello all,

I am searching column A for a value and deriving the row. Hard coding the value works as expected. I can't seem to get the code to work using a variable

Value in quotes works, "DEV ". nMatch contains the correct row #.Variable with value "DEV" doesn't work. nMatch is 0.

Dim nMatch As Long
DIM vref_sapsystem As String       " contains "[B]DEV[/B]" when the match takes place

nMatch = Application.WorksheetFunction.Match([B]"DEV"[/B], Sheets(1).UsedRange.Columns(1), 0)
    
nMatch = Application.WorksheetFunction.Match([B]vref_sapsystem[/B], Sheets(1).UsedRange.Columns(1), 0)

Thanks
Bruce

 

[Jerry Sullivan]

Hi Bruce,

Using the String Variable should provide the same result as the Constant under exactly the same conditions.

If you haven't already done so, try adding Watches, Msgboxes, or Debug.Print statements to verify your references match when both statements are run.

For example running this code will display information to the Immediate Window that can provide clues as to the problem...

Sub My_Test()
    Dim nMatch As Long
    Dim vref_sapsystem As String

    nMatch = Application.WorksheetFunction.Match("DEV",  _
        Sheets(1).UsedRange.Columns(1), 0)
    Debug.Print "Result with Constant: " & nMatch
    

    vref_sapsystem = "DEV"
    Debug.Print "Current value of vref_sapsystem: " _
        & vref_sapsystem & " Characters: " & Len(vref_sapsystem)

    nMatch = Application.WorksheetFunction.Match(vref_sapsystem, _
        Sheets(1).UsedRange.Columns(1), 0)
    Debug.Print "Result with Variable: " & nMatch
    

    Sheets(1).UsedRange.Columns(1).Interior.Color = vbYellow
    
End Sub

If this test shows nResult to be the same for both statements, then insert similar tests into your actual code.

Here's a few possible reasons why your actual code might have different results and ways to remedy.

1. If the leftmost column of UsedRange is not Column A -> Reference Column A explicitly instead of UsedRange.

2. If Sheets(1) is a different sheet than expected -> Reference the Sheet by Name explicitly

3. If a different workbook as active. -> Qualify the reference by including a Workbook object.

4. Obviously, if vref_sapsystem is not equal to "DEV" at the time the second statement is run.

I'll be interested to hear what you find.