Solved: Cell value to be used for row definition...

[Papadopoulos]

This should be easy but I can't seem to figure out the syntax.
Based on a choice in a listbox I need to look at a several cells on a different sheet.

The cell row is simply the index of the listbox choice +1. If the listbox choice yields an index of 7 then I need need to get values out of several cells in row 8 of a specific sheet.
This is the beginning of cleaning up an ugly mess

[vba]Sub setSheetCostA()
Dim indexChoice As Integer
Dim stockRow As Integer
Dim description As String ' column B
Dim price As Long ' column E
Dim code As Long ' column F
Dim stockIndex As Range

With ThisWorkbook.Worksheets("Sheet1")
indexChoice = Range("stockInd").Value
stockRow = indexChoice + 1
MsgBox "The Stockrow is " & stockRow
End With

With ThisWorkbook.Worksheets("stockRef")
MsgBox "The Stockrow is still " & stockRow
Set stockIndex = Range(.Cells(stockRow, 4)) ' I was hoping to set the stockIndex to "D stockRow" and use offsets below
Worksheets("ref").Range("sht").Value = .Range("E2") ' I could just use the column and stockRow (would replace the 2) but I can't get it to behave
Worksheets("Sheet1").Range("detStockDesc").Value = .Range("B2") ' as above
Worksheets("Sheet1").Range("stockCode").Value = .Range("F2") ' as above
End With

End Sub[/vba]
The goal is to rewrite 150 lines of if then statements with something far more flexible.

Thanks in advance for any help possible.

[shrivallabha]

In the part below
[VBA] indexChoice = Range("stockInd").Value [/VBA]
Add a dot
[VBA] indexChoice = .Range("stockInd").Value [/VBA]

[Papadopoulos]

Actually that part works fine. It's when I try and get that into the row designation that it breaks down.

Set stockIndex = Range(.Cells(stockRow, 4))

[Bob Phillips]

Just use

[vba]

Set stockIndex = .Cells(stockRow, "D")
[/vba]

[Papadopoulos]

Yes, Just figured it out. It looks like so:
[VBA]Sub setSheetCostA()
Dim indexChoice As Integer, stockRow As Integer
Dim stockIndex As Range

With ThisWorkbook.Worksheets("Sheet1")
indexChoice = Range("stockInd").Value
stockRow = indexChoice + 1
' MsgBox "The Stockrow is " & stockRow
End With

With ThisWorkbook.Worksheets("stockRef")
Set stockIndex = .Cells(stockRow, 4) ' Yes, this is correct
' MsgBox stockIndex ' this displays the value of the cell in the range
Worksheets("ref").Range("sht").Value = stockIndex.Offset(0, 1) ' Yes!
Worksheets("Sheet1").Range("detStockDesc").Value = stockIndex.Offset(0, -2) ' as above
Worksheets("Sheet1").Range("stockCode").Value = stockIndex.Offset(0, 2) ' as above
End With
End Sub
[/VBA]

Works like a charm.
I no longer have to change 55 if/then statements for each of 3 iteration of this just because the list of items has changed!

Thanks for your help.