PDA

View Full Version : Solved: Cell value to be used for row definition...



Papadopoulos
10-27-2010, 09:43 AM
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

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
The goal is to rewrite 150 lines of if then statements with something far more flexible.

Thanks in advance for any help possible.

shrivallabha
10-27-2010, 10:26 AM
In the part below
indexChoice = Range("stockInd").Value
Add a dot
indexChoice = .Range("stockInd").Value

Papadopoulos
10-27-2010, 10:55 AM
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
10-27-2010, 01:05 PM
Just use



Set stockIndex = .Cells(stockRow, "D")

Papadopoulos
10-27-2010, 01:41 PM
Yes, Just figured it out. It looks like so:
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


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.