How to use a string inside a .Range in Excel VBA?

Question

I would like your help with something I got stuck.

Basically I want to do a variable iRow, by finding a cell with the value OBRC, the cell that has this value is retrieved by the command find, then I got the value from the row and treat it as a string manipulating the string to get only the row number as exit, till this part was not a big deal. But when I need to use this string variable CellString as part of the range value it doesn't work.

I need this to make my script work, as I need this value to write the information on the spreadsheet, so I would like to know how can I solve it? I was thinking to convert this string as double but I don't know how to do it.

Here is my code:

Function FindCell()

 Dim CellLocation As Range
 Dim CellString As String

  
    Set CellLocation = Sheets("Sheet1").Range("A1048576").Find("OBRC") ' retrieves the value OBRC + the row.

        CellString = Right(CellLocation.Text, 0) ' Manipulate the entire value OBEC + Row and retrieves only the row.
    
    With ThisWorkbook.Sheets("Sheet1")
            .Range("A" & CellString).Value = "Teste" 'it concatenate the Column + the row, it should be working but is not
    End With
 
End Function

Fell free to give me any advice.

CellString = Right(CellLocation.Text, 0) does not return the row of the found cell, but will return and empty string. — Scott Craner
– Scott Craner, Commented Feb 21, 2020 at 21:58
Also Set CellLocation = Sheets("Sheet1").Range("A1048576").Find("OBRC") is only looking in one cell A1048576. If that is the cell you want then you can skip the whole thing and just do .Range(A1048576).Value = "Teste" but something tell me you are loooking for it in Sheets("Sheet1").Range("A:A").Find("OBRC") — Scott Craner
– Scott Craner, Commented Feb 21, 2020 at 22:00
@ScottCraner you're totally right your answer is exactly what i was looking for. Thanks for you help !! — Lucas Silva
– Lucas Silva, Commented Feb 21, 2020 at 23:10

Scott Craner · Accepted Answer · 2020-02-21 22:05:09Z

1

see notes:

Function FindCell()
    Dim CellLocation As Range
    Set CellLocation = Sheets("Sheet1").Range("A:A").Find("OBRC") 'Returns the cell in which OBRC is found

    If Not CellLocation Is Nothing Then
        Dim CellString As Long
        CellString = CellLocation.Row 'Get the Row.

        With ThisWorkbook.Sheets("Sheet1")
            .Range("A" & CellString).Value = "Teste"
        End With
    End If
End Function

I left it as a Function, but if one is not going to return a value to an outside source then this should be a Sub()

answered Feb 21, 2020 at 22:05

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lucas Silva Over a year ago

Thanks for your attention, have a nice day!!

Collectives™ on Stack Overflow

How to use a string inside a .Range in Excel VBA?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related