CODEWITHSUNDEEP
vbaexcelexcel-2007
Manivannan KG
Manivannan KG

Reputation: 371

VBA-get values with known cell address

I am trying to achive the values of a row holding specific text. Say Liquidity Coverage Ratio is the text that is available in B57. I need the value of C57 and D57. I have achieved until retreiving the address of the cell holding text.

Kindly help me progressing further.

 If fCheckSheet(forecastWorkbook, "Calculator (FX net)") Then
            wsForecast.Activate
        Else
            ErrorStatus = "Source Sheet:Calculator (FX net) not found"
            msgBoxReturn = MsgBox(ErrorStatus & forecastWorkbook.FullName, vbExclamation + vbOKCancel)
     End If

     Set rngRatio = FindRangeOfText(wsForecast, "Liquidity Coverage Ratio")
     'Gets the address of cell having
     RatioAddress = rngRatio.Address

     'The address is $B$57
       '???? how to retreive values for $c$57 and $D$57

I really appreciate the response from stackoverflow users who made me grow from novice until this level. Thanks for your patience.

Regards,

Mani

Upvotes: 0

Views: 1398

Answers (1)

David Zemens
David Zemens

Reputation: 53663

Assuming that the functions you refer to (but did not provide the code to support) will return Range type object (or Nothing), then you can simply use the Offset method:

If Not rngRatio Is Nothing Then
    With rngRatio
        Debug.Print .Offset(0,1).Address, .Offset(0,1).Value '$C$57
        Debug.Print .Offset(0,2).Address, .Offset(0,2).Value '$D$57
    End With
End If

There is also a lesser-known way to do the same thing:

If Not rngRatio Is Nothing Then
    With rngRatio
        Debug.Print .Cells(1, 2).Value 
        Debug.Print .Cells(1, 3).Value
    End With
End With

My preference is for the first method, because it is explicit rather than implicit. This method is less clear, because not everyone knows intuitively that you can reference a cell outside of a range object, using the Cells property, such as:

MsgBox Range("A1").Cells(1, 4).Address ' --> $E$1

MsgBox Range("A1").Cells(4, 2).Address ' --> $B$4

Upvotes: 2

Related Questions