MrExcel Publishing
Your One Stop for Excel Tips & Solutions

V-Lookup and Macros


Posted by John DerGarabedian on June 06, 2001 10:15 AM

I have an application where I need to retrive a file name from a lookup table of 50 states (plus DC), take this result and open a file from the Internet using this file name.

I have written a macro using the auto-record feature in Excel. The problem I have is whatever state is selected when I write the macro is the state that is ALWAYS opened. In other words, the result from the lookup table is ignored by the macro. The macro simply locks in the file name that is selected when the macro is written. I need this to change as reflected by the lookup table.

Please help. THANK YOU.


Posted by Billy on June 06, 2001 10:23 AM


It would help if you could post your code

Posted by John DerGarabedian on June 06, 2001 1:01 PM

This is the code as recorded by Excel

ActiveSheet.Unprotect
Workbooks.open fileName:=_
"http://www.irs.ustreas.gov (Etc.)"
ActiveWindow.Close
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
End Sub

The File name is the variable I need to retreve from the look up table.

THANK YOU.

Posted by Russell on June 06, 2001 1:10 PM

Where is the value you are looking up?

When you run the code, is the state (or DC) somewhere on a worksheet? I don't mean your lookup table, I mean how do you decide what state you are opening the web page for?

Posted by John DerGarabedian on June 06, 2001 1:37 PM

Re: Where is the value you are looking up?

I will be building a drop down box shortly. For now, the state abbreviation is entered on a seperate (linked)sheet. The lookup table takes this and returns its value (the web address). I need the macro to open the file at that web address.