CODEWITHSUNDEEP
excelvba
Lucas Silva
Lucas Silva

Reputation: 5

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

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.

Upvotes: 0

Views: 578

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

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()

Upvotes: 1

Related Questions