Reputation: 371
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
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