Problems identifying row / column position

[Mark.B]

Hi there, I am using the follwing code to identify the row/column position of a particular value within a range of values (in this example, it is a value ranging from 1 to 10).

The probelm is however, If the 'Level' = 1, then the code below returns the row / column position for the value 10. Does anyone know what I am doing worng and how I can correct this.

Regards
Mark


Level = Range("A1").Value

With Worksheets(1).Range("B1:B10")
Set c = .Find(Level, LookIn:=xlValues)
If Not c Is Nothing Then
Level_Address = c.Address(RowAbsolute:=False)
Level_Row = c.Row
Level_Col_Num = c.Column
Level_Col_Let = Mid(c.Address, 2, (InStr(2, c.Address, "$")) - 2)
End If
End With

 

[erik.van.geit]

Hi,

the method "FIND" will always use the settings which were used before - be it manually or by VBA - therefore it's good practive to specify all relevant arguments

the "1" is part of "10" so your code probably looked to cell"parts"
so, add an argument "LOOKAT"

Set c = .Find(Level, LookIn:=xlValues, Lookat:=xlWhole)

take a look in the helpfiles about "FIND"

kind regards,
Erik

 

[Zack Barresse]

Try only looking at the Whole part of the cell value..

    Dim c As Range, ws As Worksheet
    Dim Level_Address As String, Level_Row As Long
    Dim Level_Col_Num As Long, Level_Col_Let As String
    Set ws = Worksheets(1)
    With ws.Range("B1:B10")
        Set c = .Find(what:=ws.Range("A1").Value, lookat:=xlWhole)
        If Not c Is Nothing Then
            Level_Address = c.Address(0)
            Level_Row = c.Row
            Level_Col_Num = c.Column
            Level_Col_Let = Left(ws.Cells(1, c.Column).Address(0, 0), Len(ws.Cells(1, c.Column).Address(0, 0)) - 1)
        End If
    End With

 

[Mark.B]

Many thanks,

Both suggestions worked.

Regards

Mark.