select different workbook in vba?

[pleasehelp]

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.

 

[FSt1]

hi
try entering this line at the start of your code..
Workbooks("test1.xls").activate
then at the end of your code it might be a good idea to go back to the
workbook that has the code.

Regards
FSt1

 

[Dave Peterson]

First, watch your typing.

The range("B19") in this line may not be on the sheet you want.

.Range(sOldPriceCol).Value =
WorksheetFunction.VLookup(Range("B19").Value,

I'm guessing that you wanted the B19 on the activesheet. If that's true, then
qualify that Range() with a leading dot:

.Range(sOldPriceCol).Value =
WorksheetFunction.VLookup(.Range("B19").Value,

That means this belongs to the object in the previous "with" statement--in this
case that's the activesheet.

And you may find that using a limited range (not all the rows and columns) makes
things work a bit nicer...

Option Explicit
Private Sub OldPriceLookup()

Dim wkbk As Workbook
Dim LookUpRng As Range

'include the extension if the workbook has been saved
'and it has to be already open, too
Set wkbk = Workbooks("test1.xls")

With wkbk.workSheets("casing")
'why use all the columns if you're only retrieving
'from column 30
Set LookUpRng = .Range("A1").Resize(1, 30).EntireColumn
End With

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 _
= Application.VLookup(.Range("B19").Value, _
LookUpRng, 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 _
= Application.VLookup(.Range("B19").Value, _
LookUpRng, 30, False)

===============
It kind of looks like you could check the last two characters and use that as
the column into that lookup range.

If that's true...



Option Explicit
Private Sub OldPriceLookup()

Dim wks As Worksheet
Dim LookUpRng As Range
Dim WhichCol As Long
Dim res As Variant
Dim myVal As Variant

'I really want to refer to the worksheet--not the workbook.
Set wks = Workbooks("test1.xls").Worksheets("Casing")

With wks
'just a single column now
Set LookUpRng = .Range("A1").EntireColumn
End With

With ActiveSheet
If .Range("B17").Value = "n/a" Then
' clear out the old price
.Range(soldpricecol).Value = ""
ElseIf .Range("b17").Value Like "A##" Then
WhichCol = CLng(Right(.Range("B17").Value, 2))
If WhichCol > .Columns.Count Then
'too far out!
Beep 'and a msgbox???
myVal = "Invalid Number" 'or "" to clear the cell???
Else
res = Application.Match(.Range("b19").Value, LookUpRng, 0)
If IsError(res) Then
myVal = "Not found!"
Else
myVal = wks.Columns(WhichCol).Cells(1).Offset(res - 1)
End If
End if
.Range(soldpricecol).Value = myVal
End If
End With
End Sub

 

[Dave Peterson]

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