Find a string and get the numerical value to the right of it

Solution 1:

You will need to adapt this but I see two ways of doing. Without knowing the answer, you could just use formulas.

Formula

Use a VLOOKUP ... Cell D2 = =VLOOKUP(D1,$A$1:$B$20,2,FALSE)

VBA

You need to change the column you want to search and the amount of columns you want to offset by to get the value.

You also need to adapt it to your solution. I've merely given you the approach and a couple of samples.

Using MATCH ...

Public Sub FindStringAndOffset()
    Dim strToSearchFor As String, lngRow As Long, objSheet As Worksheet
    Dim varValue As Variant, strColToSearchIn As String, lngOffset As Long
    
    strToSearchFor = "Lookup 8"
    strColToSearchIn = "A"
    lngOffset = 1
    
    Set objSheet = Sheet1
    
    With objSheet
        On Error GoTo ExitGracefully:
        
        lngRow = WorksheetFunction.Match(strToSearchFor, .Range(strColToSearchIn & ":" & strColToSearchIn), 0)
        varValue = .Range(strColToSearchIn & lngRow).Offset(0, lngOffset).Value
        
        Debug.Print varValue
    End With
    
ExitGracefully:

End Sub

... or using a VLOOKUP ...

Public Sub FindStringAndOffsetv2()
    Dim strToSearchFor As String, objSheet As Worksheet
    Dim varValue As Variant, strColToSearchIn As String, lngOffset As Long
    Dim strColumnWithOffset As String
    
    strToSearchFor = "Lookup 8"
    strColToSearchIn = "A"
    lngOffset = 1
    
    Set objSheet = Sheet1
    
    With objSheet
        On Error GoTo ExitGracefully:
        
        strColumnWithOffset = Split(.Range(strColToSearchIn & "1").Offset(0, lngOffset).Address, "$")(1)
        varValue = WorksheetFunction.VLookup(strToSearchFor, .Range(strColToSearchIn & ":" & strColumnWithOffset), lngOffset + 1)
        
        Debug.Print varValue
    End With
    
ExitGracefully:

End Sub

There are a heap of ways to skin that cat.