Macros not performing correctly

I recorded a macro to place a vlookup formula in four cells that are side by 
side. When I run the macro it only performs the first task, it does not go on 
to do the other three. I used the tab key to go to the next cell while 
recording the macro, I don't know if that's why it's not working or if it's 
something else. 
The script looks like this:
Sub EERates()
'
' EERates Macro
' Macro recorded 3/4/2005 by BCBSOK
'

'
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Sheet1!R3C1:R102C2,2,0)"
    Range("F15").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],Sheet1!R3C4:R102C5,2,0)"
    Range("G15").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],Sheet1!R3C7:R102C8,2,0)"
    Range("H15").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],Sheet1!R3C10:R102C11,2,0)"
    Range("E16").Select
End Sub



Any help would be appreciated!

Thanks!
0
fabulousk (5)
3/4/2005 10:25:02 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
685 Views

Similar Articles

[PageSpeed] 45

If you intended to be using a single range to function with all of the
VLOOKUP formulas, you did not get that.......you evidently entered your
lookup range as "relative referenced" cells, rather than "absolute
referenced" cells, or better yet, a RangeName.........try something like:

=VLOOKUP(D13,$A$3:0$B$102,2,0)

Vaya con Dios,
Chuck,CABGx3




"fabulousk" <fabulousk@discussions.microsoft.com> wrote in message
news:6B2E0EBA-BF10-49AB-8269-CE8B0E85232F@microsoft.com...
> I recorded a macro to place a vlookup formula in four cells that are side
by
> side. When I run the macro it only performs the first task, it does not go
on
> to do the other three. I used the tab key to go to the next cell while
> recording the macro, I don't know if that's why it's not working or if
it's
> something else.
> The script looks like this:
> Sub EERates()
> '
> ' EERates Macro
> ' Macro recorded 3/4/2005 by BCBSOK
> '
>
> '
>     ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Sheet1!R3C1:R102C2,2,0)"
>     Range("F15").Select
>     ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],Sheet1!R3C4:R102C5,2,0)"
>     Range("G15").Select
>     ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],Sheet1!R3C7:R102C8,2,0)"
>     Range("H15").Select
>     ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],Sheet1!R3C10:R102C11,2,0)"
>     Range("E16").Select
> End Sub
>
>
>
> Any help would be appreciated!
>
> Thanks!


0
croberts (1377)
3/4/2005 10:44:28 PM
My Vlookup formula is performing correctly. 
I believe the problem lies in turning the relative reference button off/on. 
At home, in a newer version of Excel, it gave me a relative reference button 
and my formula worked. At work, I do not get a relative reference button and 
it's not working. 
How do I get the relative reference button? I'm using Excel 2000 at work, 
maybe it does not have such a button? 

"CLR" wrote:

> If you intended to be using a single range to function with all of the
> VLOOKUP formulas, you did not get that.......you evidently entered your
> lookup range as "relative referenced" cells, rather than "absolute
> referenced" cells, or better yet, a RangeName.........try something like:
> 
> =VLOOKUP(D13,$A$3:0$B$102,2,0)
> 
> Vaya con Dios,
> Chuck,CABGx3
> 
> 
> 
> 
> "fabulousk" <fabulousk@discussions.microsoft.com> wrote in message
> news:6B2E0EBA-BF10-49AB-8269-CE8B0E85232F@microsoft.com...
> > I recorded a macro to place a vlookup formula in four cells that are side
> by
> > side. When I run the macro it only performs the first task, it does not go
> on
> > to do the other three. I used the tab key to go to the next cell while
> > recording the macro, I don't know if that's why it's not working or if
> it's
> > something else.
> > The script looks like this:
> > Sub EERates()
> > '
> > ' EERates Macro
> > ' Macro recorded 3/4/2005 by BCBSOK
> > '
> >
> > '
> >     ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Sheet1!R3C1:R102C2,2,0)"
> >     Range("F15").Select
> >     ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],Sheet1!R3C4:R102C5,2,0)"
> >     Range("G15").Select
> >     ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],Sheet1!R3C7:R102C8,2,0)"
> >     Range("H15").Select
> >     ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],Sheet1!R3C10:R102C11,2,0)"
> >     Range("E16").Select
> > End Sub
> >
> >
> >
> > Any help would be appreciated!
> >
> > Thanks!
> 
> 
> 
0
fabulousk (5)
3/7/2005 3:17:06 PM
I found it on another post. It has to do with the stop recording toolbar.
Yeah! It's working now. 

"fabulousk" wrote:

> My Vlookup formula is performing correctly. 
> I believe the problem lies in turning the relative reference button off/on. 
> At home, in a newer version of Excel, it gave me a relative reference button 
> and my formula worked. At work, I do not get a relative reference button and 
> it's not working. 
> How do I get the relative reference button? I'm using Excel 2000 at work, 
> maybe it does not have such a button? 
> 
> "CLR" wrote:
> 
> > If you intended to be using a single range to function with all of the
> > VLOOKUP formulas, you did not get that.......you evidently entered your
> > lookup range as "relative referenced" cells, rather than "absolute
> > referenced" cells, or better yet, a RangeName.........try something like:
> > 
> > =VLOOKUP(D13,$A$3:0$B$102,2,0)
> > 
> > Vaya con Dios,
> > Chuck,CABGx3
> > 
> > 
> > 
> > 
> > "fabulousk" <fabulousk@discussions.microsoft.com> wrote in message
> > news:6B2E0EBA-BF10-49AB-8269-CE8B0E85232F@microsoft.com...
> > > I recorded a macro to place a vlookup formula in four cells that are side
> > by
> > > side. When I run the macro it only performs the first task, it does not go
> > on
> > > to do the other three. I used the tab key to go to the next cell while
> > > recording the macro, I don't know if that's why it's not working or if
> > it's
> > > something else.
> > > The script looks like this:
> > > Sub EERates()
> > > '
> > > ' EERates Macro
> > > ' Macro recorded 3/4/2005 by BCBSOK
> > > '
> > >
> > > '
> > >     ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Sheet1!R3C1:R102C2,2,0)"
> > >     Range("F15").Select
> > >     ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],Sheet1!R3C4:R102C5,2,0)"
> > >     Range("G15").Select
> > >     ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],Sheet1!R3C7:R102C8,2,0)"
> > >     Range("H15").Select
> > >     ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],Sheet1!R3C10:R102C11,2,0)"
> > >     Range("E16").Select
> > > End Sub
> > >
> > >
> > >
> > > Any help would be appreciated!
> > >
> > > Thanks!
> > 
> > 
> > 
0
fabulousk (5)
3/7/2005 3:23:03 PM
Glad to  hear all is well..............thanks for the feedback

Vaya con Dios,
Chuck, CABGx3



"fabulousk" <fabulousk@discussions.microsoft.com> wrote in message
news:F0113667-DBA4-4F86-AE1A-DED2DD0CA0F7@microsoft.com...
> I found it on another post. It has to do with the stop recording toolbar.
> Yeah! It's working now.
>
> "fabulousk" wrote:
>
> > My Vlookup formula is performing correctly.
> > I believe the problem lies in turning the relative reference button
off/on.
> > At home, in a newer version of Excel, it gave me a relative reference
button
> > and my formula worked. At work, I do not get a relative reference button
and
> > it's not working.
> > How do I get the relative reference button? I'm using Excel 2000 at
work,
> > maybe it does not have such a button?
> >
> > "CLR" wrote:
> >
> > > If you intended to be using a single range to function with all of the
> > > VLOOKUP formulas, you did not get that.......you evidently entered
your
> > > lookup range as "relative referenced" cells, rather than "absolute
> > > referenced" cells, or better yet, a RangeName.........try something
like:
> > >
> > > =VLOOKUP(D13,$A$3:0$B$102,2,0)
> > >
> > > Vaya con Dios,
> > > Chuck,CABGx3
> > >
> > >
> > >
> > >
> > > "fabulousk" <fabulousk@discussions.microsoft.com> wrote in message
> > > news:6B2E0EBA-BF10-49AB-8269-CE8B0E85232F@microsoft.com...
> > > > I recorded a macro to place a vlookup formula in four cells that are
side
> > > by
> > > > side. When I run the macro it only performs the first task, it does
not go
> > > on
> > > > to do the other three. I used the tab key to go to the next cell
while
> > > > recording the macro, I don't know if that's why it's not working or
if
> > > it's
> > > > something else.
> > > > The script looks like this:
> > > > Sub EERates()
> > > > '
> > > > ' EERates Macro
> > > > ' Macro recorded 3/4/2005 by BCBSOK
> > > > '
> > > >
> > > > '
> > > >     ActiveCell.FormulaR1C1 =
"=VLOOKUP(RC[-2],Sheet1!R3C1:R102C2,2,0)"
> > > >     Range("F15").Select
> > > >     ActiveCell.FormulaR1C1 =
"=VLOOKUP(RC[-3],Sheet1!R3C4:R102C5,2,0)"
> > > >     Range("G15").Select
> > > >     ActiveCell.FormulaR1C1 =
"=VLOOKUP(RC[-4],Sheet1!R3C7:R102C8,2,0)"
> > > >     Range("H15").Select
> > > >     ActiveCell.FormulaR1C1 =
"=VLOOKUP(RC[-5],Sheet1!R3C10:R102C11,2,0)"
> > > >     Range("E16").Select
> > > > End Sub
> > > >
> > > >
> > > >
> > > > Any help would be appreciated!
> > > >
> > > > Thanks!
> > >
> > >
> > >


0
croberts (1377)
3/7/2005 4:25:10 PM
Reply:

Similar Artilces:

Help, need to speed up this macro
Forgive because this will be a lot of code. The overall point to all of this code is to update the header and footer based upon entires made on the HeaderPage worksheet. The code pulls the entries made and populates the header and footer on all worksheets with in the workbook. The issue is that it has to loop through each worksheet when activated and can take some time to complete. Is there anything I can do to this to speed it up? The code below is found in two parts. The following code is found in ThisWorkbook: Code: -------------------- Private Sub Workbook_BeforePrint(Cancel As B...

Deleting a macro
I am currently not well versed in Macro's for Excel. One thing I'd like to know how to do is delete a Macro that I essentially cannot find. Here's what I did: On an excel sheet I've been working with, went to: >> Tools >> Macro >> Visual Basic Editor. From there I went to Include >> Module [following the steps in Help]. But, I then cancelled out of it by clicking on the close button [the X in top right], and then closed the SpreadSheet [and saved when it asked]. But now when I start up it asks me if I want to enable Macros. When I do enable th...

Solve macro protection annoyance
Recently, I included a macro in my favorite spread sheet. Unfortunately, this macro triggers the macro protection features in Excel 2002. Now I'm really getting tired of closing that damned warning box. I suppose I could delete it, but the macro makes it possible for me easily see the currently active cell. I honestly cannot do without it. Another option would be to lower my security to "low", but the consequences of running a bad macro could be disastrous. So I have searched the net for answers. The answer seems to be to digitally sign my macro with some applets inc...

Macro calling another Macro
Is it possible to call one macro within another macro? If so, how? The macros would be located in the same workbook, but different sheets Thanks, John Sub Macro1() Do stuff here maybe Call Macro2() Perhaps do more stuff here. End sub -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) -------------------------------------------------------------...

Macros #18
All, I have a template that I put together a marco in. Whenever I send it to my colleage the macro does not work. How do I make the macro travel with the template or gets copied whenever I copy the template into other drives. Thanks. The macro lives in the workbook/template--so if you shared the workbook/template as a normal .xls or .xlt or .xla workbook/template, then the macro made it there. I'd ask the recipient if they allow macros to run--tell them to check their security settings. If you have more questions, be sure to share the version of excel that they're running. alish ...

E-mail macro
Hi all, I have a workbook that I send out daily to a contact list withi outlook. What is the best way of doing this with a macro to work off a button o the worksheet. Thanks in advance -- greg746 ----------------------------------------------------------------------- greg7468's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=903 View this thread: http://www.excelforum.com/showthread.php?threadid=46988 Here's how to send a workbook from Excel as an attachment: Create a macro with the code below and then create a button that calls that macro. Keep in min...

Disable viewing of macros
Hi, 1. In general, how can i disable users from accessing macros for a particular workbook. I guess i should be putting some code in the file open event and close event. What is the command to disable the option. 2. I have a workbook with many worksheets. Each worksheet is protected but for some cells that are allowed to be edited by the user. Therefore, if a user wants to edit all the cells then he/she should know the unprotect worksheet password. How can i disable a user from accessing any macro of the workbook unless the worksheet is unprotected. Any help is appreciated. Thanks....

Excel Macro VBA Help Needed
I have a sheet which has been copied from a pivot table. I need it to perform the following: Find a cell with specific text (Yes) Move over three columns (I have been using an offset) Write a formula that subtotals data that is below this summation row Copies this formula to right until the column is empty Display this data onto a new summary sheet Ultimately, this would be a loop that looks in column C for some wildcard, subtotals and then, moves over to column B and totals the previously calc'd subtotals. I seem to be able to get to the specific text cell and use the offset. I am str...

Macros disappear after a file is imported
Good afternoon everybody, I am working on a program that exports data from an Access query to an Excel spreadsheet. I then need to run an Excel macro on that spreadsheet. Export works fine and can see exported data in an Excel spreadsheet fine. But..... If I open a standard excel spreadsheet alone and click on my macros menu, I can see a list of macros, but if a spreadsheet is exported from Access to Excel AND Excel gets opened on an automatic basis by a VBA command, then I can see my exported data, but my list of macros is blank and seems unavailable. Anybody know why? (I know Excel m...

Maro warning banner, despite no macros present?
I have a couple of PP presentations that I made to use for work. I will call them PP1 and PP2. With PP1, the macro warning banner appears in the .ppt and ..pps versions of this file, despite the fact that there are no macros. I have gone to the .ppt version of PP1 and removed the macros under tools and VBA editor, and even lowered the security settings, yet I still get the warning banner. I have another PP presentation that works great, PP2, however it takes about 15 seconds to open in the .pps version. I thought that perhaps the screen shots I put in PP2 made for a large fil...

Improve 2008 SBS performance?
Customer has a 2008 SBS, but isn't using SQL, Exchange, Sharepoint - it's basically a file & print server. (Their reasoning was that they may want to use those features in the future) Can I disable things not being used to improve the performance of the server? They are running a network version of Peachtree accounting that is painfully slow! You can. But it may not solve your issue with slow access to such as Peachtree. We could tell better if we had some stats, but there are a number of tools you can use to diagnose bottlenecks on station to server communications. ...

Macro for refresh folder
Hi All, Would like to know how to write a macro in outlook to click on tools -> refresh this folder. Please help. With kind regards Oracleinform ...

Mortgage Account setup wrongly
I have a Mortgage account with a wrong opening balance, and wrong first payment transaction. My payment schedules are correct. I would like to correct each entry of this account. Would this mess out my account, payment schedules and historical reconciled payments? Most likely if you try to make changes to previous transactions, something will get messed up. How you deal with this all depends upon how badly you want to keep the transactions linked between the accounts. Personally, I don't like any linked transactions if I can avoid it, so I would either just make an adjustment to th...

XPath performance
I got a question on executing the xpath. Can someone let me know which way is faster for the following two scenarios (one with XmlDocument and the other using XmlDocument.CreateNavigator())? XmlDocument doc = new XmlDocument(); doc.Load(input); 1. XmlNodeList nodes = doc.SelectNodes(xpathexp); 2. XmlNodeList nodes = doc.CreateNavigator().Select(xpathexp); The doc.SelectNodes is a convenience method that in turn calls this.CreateNavigator().Select(xpath) Note that XPathNavigator.Select returns an XPathNodeIterator and not an XmlNodeList and hence the doc.SelectNodes internally creates an...

UDFs/Macros in workbook
I am confused concerning where UDFs and/or macros must be located so that they will available to someone that I send the workbook to. Can they be in an add-in? Can they be in my Personal.xls file? Must they be in a module of the workbook? Thanks Brian Tozer Brian UDF's and Macros can reside in a General Module in the workbook you send. They could be in an Add-in which you would also send. Personal.xls is not a great idea since you would have to send that along with the workbook and the user may have his/her own Personal.xls which you would not want to overwrite or cause conflicts wi...

How to not allow cashier to perform a void?
Hi, I've done a lot of security setting for cashier already, but I can't find a way to block a cashier from not performing a void on transactions. Does anyone know a way to block cashier from doing so? Thank you. Natt You can write a custom com component to prevent this from happening. It would actually be fairly simple. >-----Original Message----- >Hi, > >I've done a lot of security setting for cashier already, but I can't find a >way to block a cashier from not performing a void on transactions. Does >anyone know a way to block cashier from do...

MS-Access 2007 Error: There is not enough memory to perform this operation..
(..couldn't locate an appropriate place for this post) A rather large database (200mb* and 12,000 objects) has been running fine as a 2003 mdb. Now, converting it to an accdb (importing all objects) routinely produces this error when compacting/repairing. It does not appear to be memory related, but perhaps some type of corruption or even something else. * links to SQL Server db for tables ...

How to execute a macro when document open
Hi. I've registered a macro, how can I execute it every time that I open the document?? Thanks Private Sub Workbook_Open() Call myMacro End Sub 'This is workbook event code. 'To input this code, right click on the Excel icon on the worksheet '(or next to the File menu if you maximise your workbooks), 'select View Code from the menu, and paste the code -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Sciamano" <Sciamano@scioman.it> wrote in message news:uMH8nDl7GHA.3960@TK2MSFTNGP05.phx.gbl... > Hi. I'...

Word wrap macro deletes formula
I created a macro to wrap text on a protected worksheet in which each cell contains a formula importing text from another worksheet. The cells are locked and formulas hidden but when I run the macro, it wraps the text and deletes the formula> how can I stop it doing this? The macro is as follows: Sub Wordwrap() ' ' Wordwrap Macro ' Macro recorded 01/10/2004 ' ' Keyboard Shortcut: Ctrl+w ' With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False ...

Macro Problems
Hello everyone. I am using Excel 2000, and I am having a macro problem. On sheet 1 I have a dropdown that I use data validation with and a macro. This list sorts alphabetically, and I can type new names in when I need to. It works fine, but I need to have two to three more dropdowns on the same sheet. I tried to modify the macro, but it will not work properly. I created a new list "VendorList" and modified the macro.(I think that is where I messed up) here is a copy of the modified macro: Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next Dim ws As Workshee...

How do I emulate rightclick on a hyperlink in an Excel Macro
What I am trying to do Write a Macro in an Excel Workbook which will enable me to: 1. Go to a web site from a hyperlink within an Excel Worksheet. 2. Identify and copy a required hyperlink which is within that web site. 3. Return to the Excel Worksheet. 4. Paste the displayed text of the copied hyperlink as text to display in the cell containing the hyperlink which led to the web site. 5. Move down one cell to the next hyperlink in the Excel Worksheet. Then repeat steps 1 to 5 above (Say 2500 times in a simple loop). Manual Keystroke Sequence Left Click on the Hyperlink. Highlight and co...

2 questions
Hi, I need help in finding where to post for windows 7. Kindly advise as to which newsgroup I should be addressing. Secondly, I was trying to uninstall a 3rd party program using "Programs and Features" in the Control Panel. It used to be called "Add/Remove Programs" in WinXP. I highlighted the program I wished to uninstall, and clicked "uninstall". That little progress circle that goes around and around drove me crazy and I waited for about 10 minutes for the OS to gather "configuration information" about the program to be uninstalled. ...

Macro to close a program
How would I set up a macro withint my database to close another program when I close my database? I don't know what Marco even is "Secret Squirrel" <secretsquirrel@discussions.microsoft.com> wrote in message news:3A72E24C-277C-4748-AA6B-5C0588103EA8@microsoft.com... > How would I set up a macro withint my database to close another program > when > I close my database? Did your Access application open the other program via automation? If so, that is the same way you should close it. Otherwise, you will probably need several API calls to find the other ...

Issue with Macro __DATE__ and UNICODE
Hi, I am trying to port a piece of code under windows mobile where UNICODE is used natively. One line is defined like this : #ifdef _UNICODE #define UL_TEXT(s) L##s #else #define UL_TEXT(s) (s) #endif const wchar_t* _Vendor = UL_TEXT("bla bla"); const wchar_t* _Version = UL_TEXT( "Library Utilities 1.10.24 " __DATE__); When I compile I get : concatenating wide "Library Utilities 1.10.24 " with narrow "Feb 19 2008" it seems __DATE__ is not expanded inside UL_TEXT. I also tried like this const wchar_t* _Version = UL_TEXT( "Library Utilitie...

problem with macro setting
hi all, i have a problem every time i open file with existing macro there an error: "Because of your security settings, macros have been disabled. To run macros, you need to reopen this workbook, and then choose to enable macros" i use excel 2007. i already change my setting in Trust Center Settings and choose Enable all macros (not recommended,....) but still failed. If i create a new one than create new module, the macro will run but if i save and close then reopen, i can't run that file... what wrong with me? many thanks reza Review the below http://...