Where do I find the commands for a macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pat - 29 Apr 2008 19:00 GMT

I recorded a macro to input a number, move right 3 spaces, enter a number,
ect .......
when I recorded it I intended to go back and replace the numbers I put in to
record with whatever I need like a question mark. Here is the macro as it is
now:
Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+a
'
ActiveCell.FormulaR1C1 = "123"
Range("E64").Select
ActiveCell.FormulaR1C1 = "123"
Range("H64").Select
ActiveCell.FormulaR1C1 = "123"
Range("K64").Select
ActiveCell.FormulaR1C1 = "123"
Range("B65").Select
End Sub

What do I replace the 123 with????

Signature

Thanks,
Pat

Reply to this Message

Zack Barresse - 29 Apr 2008 19:30 GMT

Well, first of all this probably should have been in
microsoft.public.excel.programming. Recorded macros are not very dynamic,
and there is a lot of generated code that is not needed. In your code, it
doesn't show which cell was the first (active) cell, so we don't know what
address value should be '123'. I'm assuming it is B64 as it is three cells
left of E64, your second cell.

I notice you select the next row in your cell list. Is there any rhyme or
reason to that? This code could be much more dynamic and do most of the
thinking for you. You would need to give us the parameters used to find
that cell by code though, i.e. it is the first open cell in the column, the
second blank cell in a used column of data, etc.

Also, you should not assign a keyboard shortcut which is already in use
natively, i.e. Ctrl + A (which is Select All).

Sub Macro1()
' Keyboard Shortcut: Ctrl+a
With Range("B64")
.Offset(0, 0).Value = 123
.Offset(0, 3).Value = 123
.Offset(0, 6).Value = 123
.Offset(0, 9).Value = 123
End With
End Sub

You see what we've done? There is no selecting going on at all, there is no
real need here. If you gave us more info we could help you out a little
more.

HTH

Signature

Zack Barresse

>I recorded a macro to input a number, move right 3 spaces, enter a number,
> ect .......
[quoted text clipped - 20 lines]
>
> What do I replace the 123 with????

Reply to this Message

Pat - 29 Apr 2008 20:10 GMT

I want it to start in the first blank cell in column B as I have already
fixed the headers for the first row. Then stop to allow me to enter a number
move right to column E, stop to allow entry of a number, then move to colum H
stop to allow entry, then move to column K to allow engry then go back to the
next empty cell in column b and do it all over again. I don't want it to run
automaticly when the work book is opened. I actuall want to pick the first
empty cell in column B then start the macro.

Is this the information needed?

Is there a book for dummies like me? The last time I used a spread sheet
program was in 1995 and it was lotus.

Signature

Thanks,
Pat

> Well, first of all this probably should have been in
> microsoft.public.excel.programming. Recorded macros are not very dynamic,
[quoted text clipped - 52 lines]
> >
> > What do I replace the 123 with????

Reply to this Message

Zack Barresse - 29 Apr 2008 20:38 GMT

You want to select the cell yourself? Or do you want the programming to
find the first empty cell for you? It's easy enough. Also, do you want a
different entry in each of the cells (B, E, H, K)? That could be done easy
enough as well. Or would you want the same entry in each of the cells? Do
you want to keep repeating this (going through the columns, then the next
row through the columns, then the next row through the columns, etc)? Or
would you like to do one row at a time, then run the macro again when you
choose?

Signature

Zack Barresse

>I want it to start in the first blank cell in column B as I have already
> fixed the headers for the first row. Then stop to allow me to enter a
[quoted text clipped - 80 lines]
>> >
>> > What do I replace the 123 with????

Reply to this Message

Pat - 29 Apr 2008 22:12 GMT

I will need to select the cell myself because of the change in employees. I
will be entering unique numbers in each cell. I will be doing anywhere from
3 to 30 rows for each person and then I will need to stop and do subtotals
for months, quarter, and ytd.

I appreciate your help. Looks like I may need to go back to school. I
could entertain the others with stories of "way back when".

Signature

Thanks,
Pat

> You want to select the cell yourself? Or do you want the programming to
> find the first empty cell for you? It's easy enough. Also, do you want a
[quoted text clipped - 89 lines]
> >> >
> >> > What do I replace the 123 with????

Reply to this Message

Robert McCurdy - 30 Apr 2008 14:57 GMT

That is exactly the kind of information we need and exactly the right way explained Pat.

Paste this into the sheets code module: Right click sheet tab and pick view code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = Cells(Rows.Count, 2).End(xlUp).Row _
Then ActiveCell(0, 4).Select
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column > 11 Then Cells(Rows.Count, _
2).End(xlUp)(2).Select
End Sub

The way this works is if you enter normally, so the activecell goes down to the next cell, then it should work OK. I've assumed you wish to enter on the same row and not check each column for the last cell. Don't forget to enter something so as to trigger the code when you test this.

Regards
Robert McCurdy

>I want it to start in the first blank cell in column B as I have already
> fixed the headers for the first row. Then stop to allow me to enter a number
[quoted text clipped - 65 lines]
>> >
>> > What do I replace the 123 with????

Reply to this Message

Pat - 30 Apr 2008 16:58 GMT

It starts out right, but I need to stay on the same row and enter an amount
in columns B, E, H, K. After entering is Col K I want it to return to Col B.
A B C D E F G H
I J K
1st qtr tot Qtr Fed tot Qtr MC Tot
Qtr State
Name Wages Ind Tot w/h Ind Tot SS Ind Tot
Sandra 500.00 10.00 38.25
5.00

The above is a condensed version of the columns. Columns C, D, F, G, I, J
and several after K are figured by formula.

I think I better find a book or I will drive you nice people nuts with
questions.

Signature

Thanks,
Pat

> That is exactly the kind of information we need and exactly the right way explained Pat.
>
[quoted text clipped - 83 lines]
> >> >
> >> > What do I replace the 123 with????

Reply to this Message

Robert McCurdy - 30 Apr 2008 17:41 GMT

Hi Pat, what precisely did you do with the suggestion I provided?
And how did you test it, and what happened when you did?

Hmmm.... just realized you need to have calculation (from your Tools > Options menu) set to Manual. Which I have by default. - sr
To test it just enter something in the first empty cell below your data in B column, commit it with the Enter key. - that's it.
The next cell should be automatically selected in column E.

Regards
Robert McCurdy

> It starts out right, but I need to stay on the same row and enter an amount
> in columns B, E, H, K. After entering is Col K I want it to return to Col B.
[quoted text clipped - 99 lines]
>> >> >
>> >> > What do I replace the 123 with????

Reply to this Message

Pat - 30 Apr 2008 18:42 GMT

I pasted both in the sheets code module. I tested it by entering a number in
an open cell in column b. After I hit enter the cursor moved up on row to
column F .

If I enter a no in column f and hit enter the cursor goes to column G and
waits. If I hit enter until I get to Column K and either hit enter or enter
a number and hit enter the cursor goes back to the next cell in Column B.

I changed the calculation to manual in the options. and the same thing
happened as before when I tested the code.

I appreciate your patience.

Signature

Thanks,
Pat

> Hi Pat, what precisely did you do with the suggestion I provided?
> And how did you test it, and what happened when you did?
[quoted text clipped - 108 lines]
> >> >> >
> >> >> > What do I replace the 123 with????

Reply to this Message

Robert McCurdy - 01 May 2008 06:24 GMT

Try this instead.
Keep the other _SelectionChange macro.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = Cells(Rows.Count, 2).End(xlUp).Row _
Then Target(1, 4).Select
End Sub

Here is an explanation on how these two worksheet event codes work.
When you change a cell by either deleting or adding content the _Change event is fired.
In the one above, it fires when the condition is triggered only if the change takes place at the end of B column - then move to column E on the same row.
The _SelectionChange event fires each time a different selection is made on the sheet.
In the one provided it only fires if the selected cell is in a column more than 11. And then it simply returns you to the cell immediately below your first entry in B column.
You might have more control over this if you place a value in some cell - say A1 for the column value - then use [A1] in the code instead of 11.
GL

Regards
Robert McCurdy

>I pasted both in the sheets code module. I tested it by entering a number in
> an open cell in column b. After I hit enter the cursor moved up on row to
[quoted text clipped - 121 lines]
>> >> >> >
>> >> >> > What do I replace the 123 with????

Reply to this Message