Return rown number in a range

[Ziggy12]

Hi there
The code below selects a variable length range on a worksheet. I need to return the cell contents of what list index returns in the column to the left o the selected range.

E.g if range B10:B30 is selected and listindex returns 10 then I need to return the content of A20.

Something like =Index(A10:A30,10)

Private Sub ComboBox2_Change()
ComboBox3.Value = ""
ComboBox4.Value = ""
X = ComboBox2.ListIndex
Select Case ComboBox2.ListIndex

Case "-1"
ComboBox3.RowSource = ""
Case Else
'ComboBox3.RowSource = "ClinicalGrp" & Format(ComboBox2.ListIndex + 1, "000")
Range("ServiceArea" & Format(ComboBox1.ListIndex + 1, "000")).Select

Test = "ServiceArea" & Format(ComboBox2.ListIndex + 1, "000")
End Select
End Sub

cheers
ziggy

 

[Richard Schollar]

Hi

You can use Offset and Resize:

Set rng = Range("B10:B30")

MsgBox rng.Resize(1).Offset(10,-1).Value

 

[Ziggy12]

cheers