Simple macro fails

Posted on 2012-09-11
Medium Priority
Last Modified: 2012-09-14
I recorded this simple macro with the following keystrokes.

right arrow
Ctrl-down arrow
down arrow

When I enter these keystrokes it am placed in the first empty cell in the column, which when I recorded this macro was B109.

But when I run the macro it seems to always go to 109.

Why is that "range" specified in the macro? I thought that a keystroke; as in the down arrow, was recorded as a keystroke; not as a range, yet it appears that my down arrow assigned a range to the action.

How do I record just the keystroke; without causing that action to specific the cell it last caused the cursor to move to?

I thought  maybe this issue was related to "Use Relative References" but clicking that option doesn't toggle it to "Use Absolute References".

I also found that I can only save macros as Ctrl +; there is no option available for Ctrl-Alt combinations, so it's hard to find a shortcut key combination that isn't already in use by XL.

The shortcut keystroke options may be a moot point as I am in about to upgrad to 2010 and that version may have the Ctrl-Alt option; I just thought I'd fire this off before I started.
Question by:mikecox_
  • 10
  • 9
LVL 10

Expert Comment

ID: 38389583
Replace your code with this:

Range("B2").end(xldown).formulaR1C1 = "=NOW()"

Author Comment

ID: 38389750
Thanks, that resolves the macro issue, using program code, but it doesn't explain why the recorded "down arrow" failed to work as it did when I ran it outside the macro.  

Why doesn't the down arrow record as a "down arrow" command?

I will always have this problem if I don't understand why keystrokes record as something else?
LVL 10

Expert Comment

ID: 38389789
Now that we've fixed the code, why did it happen?

The simple answer is that when you recorded the macro, the option was set to use Absolute References. Therefore, after the code selects all cells with data after B2 (the first two lines of your code), it then goes the the cell it's in (over here B109), and inserts the formula it told you to. Then, you must have pressed the tab to leave the cell, so it 'recorded' to go to C109.

Changing the setting to use Relative References may or may not help. Therefore, it would probably be a good idea to get a grasp on the simple basics of VBA (which is the language of macros). You can then record your macros, and then go straight to the code to tweak them to your liking.

If you want, I can look for a couple of good places to learn VBA.
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Assisted Solution

ragnarok89 earned 400 total points
ID: 38390773
If you ever want to move up, down, left, right a certain number of cells you can always use this line of code:


where r is the number of rows to move.
r = 3 would move DOWN 3 rows
r = -5 would move UP 5 rows

where c is the number of columns to move.
c = 1 would move RIGHT 1 column
c = -2 would move LEFT 2 columns


Author Comment

ID: 38392938
I now have Office 2010 installed on my machine.

Al, thanks for the code information, I'm sure it will come in handy as I learn more about VBA.

Etech yes, tell me where I can find good places to learn VBA.

fyi: I watched Excel 2010 Essential Training with Bob Flisser
on Linda.com as he demonstrated the use of the down arrow in a macro, just as I had done.  His worked, mine didn't.  If you have access to Linda.com he did it in:

Chapter 13: Recording and using a simple macro.   Starting at 7:05.  

At exactly 7:24 he added the down arrow to the macro and it worked!
He had the Relative option set; which seems to be the default.  But it seems like he should have had absolute set.

Two more question:

How do you get the Relative option to change to Absolute? It will not switch for me.
How do I force your macro to run when a worksheet is opened.

Author Comment

ID: 38393009

I got your code to work once; it created a time and date in Bold, but didn't move to the cursor one cell to the right and wait for data entry.  So I added


to move the cursor in the column one cell to the right and the macro stopped working.

So I took the activecell line out and the macro still wouldn't work.

Maybe I've placed your code in the wrong place?

Your code added
LVL 10

Assisted Solution

etech0 earned 1600 total points
ID: 38393082

This website looks like it has some good tutorials in VBA:

I don't remember how to set absolute/relative references in 2003, but it 2010 it's a toggle button on the Developer ribbon.

Note: if you just installed 2010, you will need to show the Developer ribbon, as it's hidden by default. To do this: click File, Options, Customize Ribbon. On the right side, check off Developer.

There are two ways to have a macro run when the workbook is opened.

The simpler way is: call the macro auto_open.

Sub auto_open()
    Range("A1").Value = "HI!"
End Sub

Open in new window

The above macro will type HI in cell A1 when the workbook is opened.

If you want to know the other way let me know and I'll tell it to you.
LVL 10

Expert Comment

ID: 38393098
And now regarding the macro at hand:

The code in your screenshot looks like it's in the same place. Where you say that the code didn't work, what do you mean? Did it do nothing? Did it do the wrong thing?

Did you add the 'offset' line before or after the line of code I gave you?

(FYI: lines of code that start with an apostrophe ( ' ) are comments. When the code is run, those lines are ignored. They are good for making notes in your code so you can remember what each line does, or why you put it there, or anything really. So in the screenshot you posted above, there are actually only three lines of code. The first starts the macro, then there's one line of code, and then End Sub, which says that it's the end of the macro.)

Author Comment

ID: 38393409
Ok, I've decided to just send the file; a B/P, Exercise spreadsheet I made for a pt that I'm trying to automate for him; so he'll keep it up!

I added notes to the macro lines.

Your macro stops at the last occupied cell, instead of the empty one below it, and keeps changing the date and time there.

I added Al's line a couple of times to move the cursor where the recorded arrow keystrokes created relative locations, so now it works!

Why does Ctrl+Home place the cursor at B2 instead of A1?
Why do the arrow keys not record as simple commands; because of Relative mode?
Why can't I shift to Absolute mode?

In the Lynda video Relative mode was used and the arrow keys seemed to work.  I've included that code for you to see.

Sorry for dragging this out )-: Appreciate you help.  I've started watching the Lynda  VBA Training videos!

Author Comment

ID: 38393427

How do I ref the ID I'm responding to?  I know it's a character put in front of the ID number but I can't recall what it is.

Thanks for the link to VBA tutorials, I've bookmarked it and will definitely check it out.

I have tried clicking on the "Use Relative References" icon on the Developer ribbon but nothing happens.

Thanks for the tips on "Sub auto_open ().
LVL 10

Accepted Solution

etech0 earned 1600 total points
ID: 38393567
I've looked at your file, and think I can answer some of your questions.

The reason control-home takes you to B2 instead of A1 is because the top row is frozen. This has got nothing to do with VBA; it's just a feature / quirk of control-home.

I looked at your code; it seems to be doing what you need it to. If you'd like, you can compact it more like this:

  Range("B2").End(xlDown).Offset(1, 0).select
  activecell.FormulaR1C1 = "=NOW()" 
  ActiveCell.Offset(0, 1).Select

Open in new window

What I've done is essentially combined a bunch of lines into fewer.

In other words, your code did this:
1. select B2
2. go to the last cell with data
3. go down one cell
4. put the date and time into the current cell
5. move over one cell
for a total of five lines of code.

Above, I've combined it into three steps, as follows:
1. find the cell under the last cell with data in column B, and select it.
2. put the time in the active cell
3. move over one cell

By compacting the code, it will run faster. Also, along the way the longer code selects cells that don't really need to be selected, so the shorter code will look nicer as it's being run.

But you don't have to worry about that much, when you're just starting out.

I'm not sure why Relative references does not seem to be working for you. However, even a basic understanding of VBA will make it not necessary, as you can clean up your recorded macros yourself, and make sure that they only do exactly what you want them to.

What you do mean by "How do I ref the ID I'm responding to? "

As far as learning VBA, what I found was most helpful for me was not videos or books or websites. It was this: I'd turn on the macro recorder, do something, and then stop the recorder and examine the code. I played around with selecting cells, changing values, formatting, saving etc., and basically anything I felt like.

That is a very good way to get a feel for what you're doing. Of course, there is a whole lot more that you can only do via direct coding, but for me that was a great start.

Also, don't expect to have to know everything about VBA. I have a pretty solid understanding of it, but I still resort to google or EE when I get stuck. There's a lot out there that can be very helpful.

Good luck, and happy learning!

Author Comment

ID: 38393615
>>What you do mean by "How do I ref the ID I'm responding to? "
I mean each message has an ID# and there is a way to ad a ref to that message in an answer (it's the next best thing to quotes) using something like cID# in the message field.  I saw it done once and ask how, but forgot the answer

Thanks a lot, I'll try you method of learning VBA; by recording keystrokes and studying them.

Author Closing Comment

ID: 38393633
Excellent, patient, support with lots of good feedback and references.
LVL 10

Expert Comment

ID: 38393639
Glad to be of help!

Author Comment

ID: 38396980
etech Something is wrong with the streamlined macro.  It changes the time in all cells containing the NOW function.  Seems like the result needs to be changed to text so the NOW doesn't change all the cells containing NOW the next time it runs
LVL 10

Expert Comment

ID: 38397134

That's actually not a problem with the macro, but with the formula.

You see, when you put a formula in a cell, any time you change another cell's value, all formulas are calculated. So the Now formula will recalculate, and display the current date and time in all of the cells containing the formula.

If you want the cell to keep the date, instead of
 ActiveCell.FormulaR1C1 = "=NOW()"

Open in new window

ActiveCell.Value = Now

Open in new window

That will make the value in the cell the actual date and time, instead of putting a formula in the cell.

Author Comment

ID: 38400190

Thanks a lot for jumping back into this closed question, and for adding another trick to my box (-;

I hesitate to press my luck with more questions but...

Why does Now work in this formula, without the (), and what does R1C1 refer to in the Formula? Or is "FormulaR1C1" just a name?
LVL 10

Expert Comment

ID: 38400208
What the old code did was put a formula into the cell. The formula was =Now(), and it recalculates to the current date whenever formulas are calculated.

The new code does something different. Instead of putting a formula into the ActiveCell, it puts a Value in. (note: that is what comes after ActiveCell. in the code)

What value does it put in? The value of Now, which is a VBA term for "Today's Date", whatever it may happen to be. So for example, if I would run it today, the cell would contain 9/14/12. But if I would run the other code today, the cell would contain =Now(), and it would keep updating.

R1C1 stands for Row 1 Column 1, and it has to do with how you reference other cells in the formula. In this case, it's totally irrelevant.

Glad to be of help! If you feel guilty for asking more questions, you can always start a new thread. Or you can try just googling it first - that's probably by biggest source of information!

Have a nice weekend!

Author Comment

ID: 38400271
Thanks again. I will not bug up again; without offering points (-:  You've worked hard for the few you got.

I had actually thought of posting this as a new question, I asked an administrator; after adding this question and he explained how I could ask a new question and reference the closed one, so I'll do that in the future.
LVL 10

Expert Comment

ID: 38400327
No problem - either way is fine!

Happy Learning!

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question