Simple macro fails
I recorded this simple macro with the following keystrokes.
ctrl-Home
right arrow
Ctrl-down arrow
down arrow
=now()
tab
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.
macro.jpg
ctrl-Home
right arrow
Ctrl-down arrow
down arrow
=now()
tab
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.
macro.jpg
ASKER
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?
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?
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
etech,
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
activecell.offset(0,1).sel
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?
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
activecell.offset(0,1).sel
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.)
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.)
ASKER
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!
Blood-pressure-record.xlsm
lynda-code.jpg
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!
Blood-pressure-record.xlsm
lynda-code.jpg
ASKER
etech
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 ().
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 ().
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
>>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.
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.
ASKER
Excellent, patient, support with lots of good feedback and references.
Glad to be of help!
ASKER
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
Blood-pressure-record.xlsm
Blood-pressure-record.xlsm
Hi!
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
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()"ActiveCell.Value = NowASKER
Awesome!
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?
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?
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!
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!
ASKER
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.
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.
No problem - either way is fine!
Happy Learning!
Happy Learning!
Range("B2").end(xldown).fo