Macro with Vlookup

I am trying to write a simple macro with a vlookup contained in it.  The 
trick is that the vlookup in the macro must be flexible to look in another 
excel file for data.  The user will decide the name of the file to go into 
the macro.  My problem is that when I recored the marco the name of the file 
I use as an example gets hard coded in, and this defeats the purpose.  
Suggestions?
0
10/15/2004 8:09:08 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
419 Views

Similar Articles

[PageSpeed] 12

Always copy/paste your code here for comments.

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"bruinsinsix" <bruinsinsix@discussions.microsoft.com> wrote in message
news:9B6C2B36-A197-498C-8279-91552AE9CE55@microsoft.com...
> I am trying to write a simple macro with a vlookup contained in it.  The
> trick is that the vlookup in the macro must be flexible to look in another
> excel file for data.  The user will decide the name of the file to go into
> the macro.  My problem is that when I recored the marco the name of the
file
> I use as an example gets hard coded in, and this defeats the purpose.
> Suggestions?


0
Don
10/15/2004 10:04:43 PM
Reply:

Similar Artilces:

Macro help with saving a spreadsheet with date and time in it
Can someone help me with some code that would save a file name as "schedule-mm-dd-yyyy-hh:mm"? Thanks, Alan Alan, how about something like this Sub Save_As() ActiveWorkbook.SaveAs Filename:="Schedule " & Format(Now, "mm-dd-yyyy-hh-mm") & ".xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Fee...

Excel 2003
I wrote the Macro myself, so I know it is safe, but I can't run it. I decreased the security to low, But I don't have a DIGITAL signature of my own to install. Can someone point me in the right direction? Many thanks in advance. Never mind - I found out that I had to shut down Excel and restart it for the change to take effect. "Jeff Lloyd" <jbl85@hotmail.com> wrote in message news:OETm0Z2PEHA.3708@TK2MSFTNGP10.phx.gbl... > I wrote the Macro myself, so I know it is safe, but I can't run it. I > decreased the security to low, But I don't have a DIG...

Excel Macro #3
I am linking a subtotal workbook to three different workbooks. I have 72 cells on each of the 3 workbooks to link to. I have created a macro basically by using the macro builder in excel, however this takes approximately 72 lines of code for each file because each file has a different name. Is there a simpler VBA code that I could use for all three files? Thanks Frank Post your code (not your workbook) here "Frank" <fborger@yahoo.com> wrote in message news:07b401c36e49$a2591180$a001280a@phx.gbl... > I am linking a subtotal workbook to three different > workbook...

#N/A error in Vlookup
I have a sheet that lookes at data in another sheet. It looks at the zip codes of the sheet and finds the match then puts in the value from the 3rd column over. The problem is that if the data doesn't have a matching zip code I get a #N/A error. I'm trying to find the formula that will leave it blank. I've used on some workbooks like isblank and such. What can I use so if there is no match it will be blank. John John, Use =IF(ISNA(lookup_formula),"",lookup_formula) or for more generic errors, replace ISNA wiuth ISERROR. -- HTH Bob Phillips "John Kr...

Nested count and vlookups
I have a list of codes that has a number associated with it on th right. I wish to write an equation that counts the number of codes o the left that fall within a certain grouping of the numbers on th right. This does not make much sense as I retype it and try to figur out a better way to explain it, so I have attached a sample of what am working on. Thanks in advance, Chris Nelso +------------------------------------------------------------------- |Filename: Sample.txt |Download: http://www.excelforum.com/attachment.php?postid=3461 +--...

VBA code for VLOOKUP
Hello, I'm looking for a way of putting a drop down box into a user form. The drop down box will work as a VLOOKUP function. The list used for the drop down box exists in another workbook. So, in a user form, I need a drop down list which populates itself from another workbook. Once a user clicks on an entry in the list, the drop down list acts as a VLOOKUP function. Does anyone know how to do this? If so, please send me an example code. thanks ...

Macros do not match Digital Signature message
I sign a workbook, send it to a client, they work on it, save it, and when they try to reopen it they get this message, and the macros are disabled. They don't amend code, just work in the worksheets. They can send it back to me, I resign it ok, but then the same thing happens when I send it back. My digital signature is a legitimate, paid for, signature. Any insights or solutions appreciated. ...

Use a VBA Macro inside an Excel Cell
This is a multi-part message in MIME format. ------=_NextPart_000_02B7_01C9F6B9.C9F418D0 Content-Type: text/plain; charset="windows-1255" Content-Transfer-Encoding: quoted-printable its been helpful to me so maybe it will do good for you too: how to create a simple macro within Microsoft Excel, and then how to use = that macro to calculate a single cell value. http://sysudi.blogspot.com/2009/06/use-vba-macro-inside-excel-cell.html ------=_NextPart_000_02B7_01C9F6B9.C9F418D0 Content-Type: text/html; charset="windows-1255" Content-Transfer-Encoding: quoted-printable &l...

Macro to copy rows
Thanks for your help. I'm looking to copy certain rows and maintain the formatting. When you click on a cmd button I would like a box to come up and ask for how many rows do you want to copy. Then, copy the entire rows from say A10 and paste to A20 and placing the amount the user asked for. I also would like to be able to go back many times and add rows..so if I add 6 rows...do work somewhere else in the sheet or workbook and come back and add an additional 2 or 3 or mor. ...

Macro to clear cells
I need a macro that if I type a value in column N the cells in the same row in coloms M,J and K needs to be cleared. This is what I have at the moment but I get runtime error 13 Type mismatch. I am just starting to learn myself VBA so please help. Thanx Private Sub Worksheet_Change(ByVal Target As Range) Dim row As Integer row = Target.row If Target.Column = "NA" Then Cells(row, 10).Clear Cells(row, 11).Clear Cells(row, 13).Clear Cells(row, 14).Clear End If End Sub Private Sub Worksheet_Change(ByVal Target As Range) On E...

Sorting with macro
Hi, I'm trying to sort a list after a specified cell. The cell is in a date format and I want to see all items in the list that are before this date or if the cell in the list are empty. I also want to sort at the same way in a another column in at the sam time. My code so far is as follows, but I don't get it to work proparly. ActiveSheet.Unprotect Dim Period As Date Period = ActiveSheet.Range("b3") Dim Avslut As Date Avslut = ActiveSheet.Range("B4") ActiveSheet.ListObjects("tabell").Range.Auto...

how can I run a macro when I open a workbook?
.... hi. put your macro in the workbooks on open event. when the workbooks is opened, the macro runs. :o >-----Original Message----- >.... >. > name it auto_open filo666 wrote: > > ... -- Dave Peterson ...

using macros to draw charts
I'm trying to write a simple macro which draws a bubble chart using a few cells of data from a worksheet. I designed the chart from data on the worksheet while recording, then got rid of the chart and ran the macro. No joy. It's stopping on the first line after Charts.Add - ActiveChart.ChartType = xlBubble and I don't understand why! Anyone got any bright ideas? Hi Josie - It's not your fault. In a few cases, the macro recorder gets confused and writes down the steps out of order. For some types of chart it doesn't matter, but for bubble charts, you have to use A...

Macro to undo filters
Hi Guys, I need to write a macro that will undo all filters that are currently on a worksheetsheet. Do I have to make sure that all the filters are undone individually or is there a way I can do all at the same time? Thanks Alan Hi Alan Check out Debra's site for everything about filters http://www.contextures.com/tiptech.html -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Alan S" <anonymous@discussions.microsoft.com> wrote in message news:da0e01c3ef58$4f14f580$a501280a@phx.gbl... > Hi Guys, > > I need to write a macro that ...

Macro for Excel #2
Hi, i am a MS office excel user, i wanted a macro for a particular function. What it exactly needs to do is In a population of say 20, 10 items would total up to a particular number so the macro should identify those 10 numbers which would total up to that one particular number. IN other words i would like to know what items, sum of which would give me a specific total. I ok for the number of hours it takes i just want the formula ...

macro #2
I am pretty new to using (or attempting to use) a Macro in a workbook. I am having trouble following how and what to do to even start the process; ie If there needs to be a worksheet already in progress or can I start from a new sheet. THEN where to go from there. Can I have some help in this area? Can some one supply me with a sample MACRO? Any help would be greatly appreciated Dan The best way to get started with macros is to use the Macro Recorder. Go to the Tools menu, choose Macros, then Record New Macro. Then, carry out some simple tasks and examine the code that is produced. ...

Macros #34
I am trying to edit and create some macros in an excel spreadsheet that was created by someone else. There are macros for this spreadsheet but for some reason when I go to Tools -> Macro -> Macros it doesn't show any in the list. When I go to Tools -> Macro -> Visual Basic Editor there is code for the macros. I have some buttons on the spreadsheet that I want to assoicate with the macros but the buttons almost seem like they are locked, when i write click them nothing happens but a shortcut menu should appear. Is there maybe some special security on there I don't...

Autorun macro #2
Hi, When I used Lotus a while ago, I remember (I think) being able to put a macro named "auto.123" into a workbook and it would run as soon as that workbook was opened... perhaps to open to a menu area of a sheet. Does Excel have anything like that? TIA Mike Hi name the macro Auto_Open() -- Regards Frank Kabel Frankfurt, Germany "MikeM" <vze28cci@verizon.net> schrieb im Newsbeitrag news:vd7am09ttbt0eeil5kk9js3vjcm9crfcpk@4ax.com... > Hi, > > When I used Lotus a while ago, I remember (I think) being able to put > a macro named "auto.123" i...

Macro
Hi all, When running a macro i am getting a error as FIELD ON WINDOW IS INACTIVE OR DISABLED(Line #4) . please suggest me how to rectify that. Regards Vishy Vishy, You are probably running a macro that left the focus on a customer id or vendor id or item id that is now locked because you typed in the the id. Let me know if this is the case. -- Thanks, Matt Landis Landis Computer "If you think SBF can't do it, give us a call!" SBF Consulting, Training and HelpDesk Nationwide http://www.landiscomputer.com (717) 733-0793 x101 "rajesh" <rajesh.neela...

A macro to check range and then check another range if falset-then
Hi – I have been trying to work out a macro. I am looking for a macro that will check 2 things....then do something. The first is if a particular cell contains a given value. Lets say Sheets("sheet1").Range("D3") should equal "Year 1" if true. Next is if a range of cell values in are blank, Sheets("Sheet1").Range("D6:G48") should be blank. If both these are true then I want the macro to copy a range of cells from Sheets("Sheet2").Range("AR5:AR47") to Sheets("Sheet1").Range("D6:D48"). If false then...

Writing Macro for extracting info from other softwares #2
Hi, Is it possible to create a macro to extract information from other softwares like Chainlinx, PMS etc.? If yes, could you please explain briefly, how to do it? Thanks. ...

All macros failing part way through with different error messages
All the macros I have in various workbooks are failing - usually part-way through - with different error messages each time. When I check the debugger and try to edit the text, there seems no reason why there should be a problem. The macro has so far stopped on instructions to : - open a workbook - select a worksheet - paste a column in a different place Several times the macro has just stopped running without completing and without showing an error message. I cannot run a host of urgent daily reports without using the macros. Please help! NO way to help unless you tell us exactly...

VLookup in VBA giving error message
Hi, I'm having trouble making the vlookup function work in VBA. the values that are used are all dates, I don't know if that makes a difference. Worksheets("sheet1").range("a1").value = application.WorksheetFunction. _ Vlookup(Range("c1"), range("a:a"), 2) this gives me a run-time error '1004' Unable to get the vlookup property of the Worksheet function class so i tried this: Worksheets("sheet1").range("a1").value = application.Vlookup(Range("c1"), _ range("a:a"), 2) but this gives me a #REF ...

macros #33
Hi I am using office 2000. I had a macro on a spreadsheet, but then deleted it. When I open it I am asked if I want to enable or disable macros, despite there not being one. What can I do so that this message doesn't appear? Thanks Steve When you record a macro, Excel creates a module in which the code is stored. When you delete the macro, the module is not automatically deleted. There are instructions for finding and removing code on the following page: http://www.contextures.com/xlfaqMac.html#NoMacros Steve UK wrote: > Hi > I am using office 2000. > I had a macro on a...

Is it possible to create a macro to group?
I am trying to create a macro that will reference specific cells and then depending on the result of those cells will "group & outline" several other rows? Is this possible? Thanks, Try recording a macro when you do it once manually to see if that helps. But I think I'd try to use excel's built in Data|Subtotals. If your data that should be grouped isn't the same (but still needs to be grouped), then you'll have to do some more work. I'd insert a helper column and somehow give each group its own unique indicator. Then Data|subtotal will work nicely. ...