Where do I find the commands for a macro

[Pat]

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????

 

[Zack Barresse]

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

 

[Pat]

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.
--
Thanks,
Pat

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

 

[Zack Barresse]

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?

--
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
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.

 

[Pat]

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".

 

[Robert McCurdy]

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
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.

 

[Pat]

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.

 

[Robert McCurdy]

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.
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.

 

[Pat]

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.

 

[Robert McCurdy]

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