CODEWITHSUNDEEP
excelfindcellsvba
ManInTheMiddle
ManInTheMiddle

Reputation: 128

pass variable to cells.find and cell address as result in vba

How can i pass a variable to cells.find and get the cell address as result back?

 For Each cellName In activeListBoxelements

    Cells.Find(What:=cellName, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Select

    CellAddr = Selection.Address(False, False, xlR1C1)

    MsgBox (CellAddr)
Next cellName

What am i doing wrong here.

thanks

Upvotes: 0

Views: 1762

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149335

You need to check if the .Find is returning anything and then display the address.

Is this what you are trying? (Untested)

Dim aCell As Range, cellName As Range, activeListBoxelements As Range
Dim CellAddr As String

'
'~~> Rest of the code
'

For Each cellName In activeListBoxelements
    Set aCell = Cells.Find(What:=cellName.Value, LookIn:=xlFormulas, _
                           LookAt:=xlPart, SearchOrder:=xlByRows, _
                           SearchDirection:=xlNext, MatchCase:=False, _
                           SearchFormat:=False)

    '~~> Check if found
    If Not aCell Is Nothing Then
        CellAddr = aCell.Address(False, False, xlR1C1)
        MsgBox CellAddr
        Set aCell = Nothing
    End If
Next cellName

Upvotes: 1

Related Questions