A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Macro problems



 
 
Thread Tools Display Modes
  #1  
Old April 20th, 2010, 10:29 PM posted to microsoft.public.excel.misc
Father John
external usenet poster
 
Posts: 14
Default Macro problems

Hi I am trying to record a simple macro where if I press alt+d the location
moves 7 "page down" key strokes - about 100 rows.
However when I record the macro it resorts back to the line number that my
macro originally ended up at 7 keystrokes from the top., even if I am 1500
lines down.
How can I resolve this?

With thanks
Stephen

(My apologies if this is duplicated as I cannot see the original post after
12 hours)

  #2  
Old April 20th, 2010, 11:05 PM posted to microsoft.public.excel.misc
Dave O
external usenet poster
 
Posts: 408
Default Macro problems

When you record a macro, Excel picks up all the details: where is the
cell pointer located, etc, and assumes you want to replicate that each
time. Most likely your macro has a row in it that says something like
Range("A1").Select, which is where you were when you recorded.

To fix it, save your working file and work from a backup copy so you
don't lose data. Open the file; on the menu click Tools Macro
Macros and highlight the macro you recorded; press Edit. The Visual

Basic editor opens, and shows you the instructions you recorded. Near
the top there will likely be a Range("A1").Select command. You can
delete it, or enter an apostrophe before that command, which signals
the compiler to disregard that row.

Good luck with it!
Dave O
Eschew Obfuscation

  #3  
Old April 20th, 2010, 11:21 PM posted to microsoft.public.excel.misc
Pierre
external usenet poster
 
Posts: 223
Default Macro problems

Hi.

You would need to define your starting point as a variable, not a specific
cell.



"Father John" wrote:

Hi I am trying to record a simple macro where if I press alt+d the location
moves 7 "page down" key strokes - about 100 rows.
However when I record the macro it resorts back to the line number that my
macro originally ended up at 7 keystrokes from the top., even if I am 1500
lines down.
How can I resolve this?

With thanks
Stephen

(My apologies if this is duplicated as I cannot see the original post after
12 hours)

.

  #4  
Old April 20th, 2010, 11:24 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 19,791
Default Macro problems

Sometimes, it's better to describe what you want to do.

For instance, if I want to select the cell after the last used cell in column A,
I'd use something like:

Dim NextCell as range
dim Wks as worksheet

with wks
set nextcell = .cells(.rows.count,"A").end(xlup).offset(1,0)
end with

nextcell.select

I like to start at the bottom of the worksheet and look upwards. Some look
downward from the cell. But if there are empty cells in the range, that may not
do what I want.



Father John wrote:

Hi I am trying to record a simple macro where if I press alt+d the location
moves 7 "page down" key strokes - about 100 rows.
However when I record the macro it resorts back to the line number that my
macro originally ended up at 7 keystrokes from the top., even if I am 1500
lines down.
How can I resolve this?

With thanks
Stephen

(My apologies if this is duplicated as I cannot see the original post after
12 hours)


--

Dave Peterson
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:24 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.