If you can determine the column to bring back based on the value in that cell,
then make a change.

WhichCol = CLng(Right(.Range("B17").Value, 2))
becomes
WhichCol = CLng(Right(.Range("B17").Value, 2)) + 27

(I forgot to add that 27 so that 2--29, 3--30, ....)

pleasehelp wrote:

i would like this code to point to workbook "test1" instead of ActiveWorkbook.

does this code need rewriten? everything i have tried has not worked. i am
very much a novice at vba. this code works well if the information remains in
the ActiveWorkbook, but for security it needs to be moved.

Private Sub OldPriceLookup()

With ActiveSheet

If .Range("B17").Value = "n/a" Then

' clear out the old price
.Range(sOldPriceCol).Value = ""

ElseIf .Range("B17").Value = "A02" Then

' A02 = 29
' set the old price (only need to change the column Number "29")
.Range(sOldPriceCol).Value =
WorksheetFunction.VLookup(Range("B19").Value,
ActiveWorkbook.Sheets("casing").Range("1:65536"), 29, False)

ElseIf .Range("B17").Value = "A03" Then

' A03 = 30
' set the old price (only need to change the column Number "30")
.Range(sOldPriceCol).Value =
WorksheetFunction.VLookup(Range("B19").Value,
ActiveWorkbook.Sheets("casing").Range("1:65536"), 30, False)

it does have an end, just not shown.

--

Dave Peterson