Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
select different workbook in vba?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
select different workbook in vba?
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 "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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
select different workbook in vba?
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 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
select different workbook in vba?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select sheet tabs in workbook & save to separate workbook files | Excel Worksheet Functions | |||
workbook select | Excel Discussion (Misc queries) | |||
select workbook | Excel Programming | |||
Sheets select method fails when workbook is opened by another workbook | Excel Programming | |||
Select other workbook to select data in a macro. | Excel Programming |