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.
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.