Recording Macros

  • Thread starter Thread starter Ed C
  • Start date Start date
E

Ed C

Can I get help in this newsgroup with recording macros?

I have recorded the following macro using the macro recorder. The cursor was
'in' cell E7

Selection.Cut
Range("E5").Select
ActiveSheet.Paste
Range("E10").Select

I would now like to convert this to a general purpose macro. I need help
using [R][C] notation.

The steps are simple:


For i = 1 to 100
(1) Using the active cell, cut its value (a constant)
(2) Move up 2 rows
(3) Paste value
(4) Move down 5 rows.
Next i

How do I change the recorded macro?

Thanks for help.

EdC
 
something like this. UNTESTED

for i=1 to 100 step 5
cells(i,"e").cut cells(i-2,"e")
next i
 
Why does this work? Am I missing something? Where's the paste statement?

EdC
Don Guillett said:
something like this. UNTESTED

for i=1 to 100 step 5
cells(i,"e").cut cells(i-2,"e")
next i

--
Don Guillett
SalesAid Software
(e-mail address removed)
Ed C said:
Can I get help in this newsgroup with recording macros?

I have recorded the following macro using the macro recorder. The cursor was
'in' cell E7

Selection.Cut
Range("E5").Select
ActiveSheet.Paste
Range("E10").Select

I would now like to convert this to a general purpose macro. I need help
using [R][C] notation.

The steps are simple:


For i = 1 to 100
(1) Using the active cell, cut its value (a constant)
(2) Move up 2 rows
(3) Paste value
(4) Move down 5 rows.
Next i

How do I change the recorded macro?

Thanks for help.

EdC
Click to expand...
Click to expand...
 
Something that may help you, as you record macros, is the Relative
Reference button on the Stop Recording toolbar. When activated, it
records movement, rather than specific cells.

If you have VBA help installed, you can select the word Cut in the code,
and press the F1 key. This will open help, at the page with information
about the Cut method. It explains that you can specify a destination for
the cut cells, as Don did.

His code is designed to work in column E, so if you want this to work in
the active column, you could add code to calculate that. For example:

'=================================
Sub CutValues()
Dim i As Integer
Dim r As Long
Dim col As Integer
r = ActiveCell.Row
col = ActiveCell.Column

If r < 3 Then
MsgBox "Please select a cell in row 3 or higher"
Exit Sub
End If

For i = 1 To 100
Cells(r, col).Cut (Cells(r - 2, col))
r = r + 5
Next i
End Sub
'===============================

Ed said:
Why does this work? Am I missing something? Where's the paste statement?

EdC
something like this. UNTESTED

for i=1 to 100 step 5
cells(i,"e").cut cells(i-2,"e")
next i

--
Don Guillett
SalesAid Software
(e-mail address removed)
Can I get help in this newsgroup with recording macros?

I have recorded the following macro using the macro recorder. The cursor
was

'in' cell E7

Selection.Cut
Range("E5").Select
ActiveSheet.Paste
Range("E10").Select

I would now like to convert this to a general purpose macro. I need help
using [R][C] notation.

The steps are simple:


For i = 1 to 100
(1) Using the active cell, cut its value (a constant)
(2) Move up 2 rows
(3) Paste value
(4) Move down 5 rows.
Next i

How do I change the recorded macro?

Thanks for help.

EdC
Click to expand...
Click to expand...
Click to expand...
 
Thank you very much, Debra!!!!

I looked all over for the way to record in relative reference. Used it in
Excel97 but couldn't find it in current XP version. Will look again.

I was totally disappointed with VBA help. Did just what you said; highlight
and F1. Worthless!!!! Didn't use cut but did try delete, selection, etc.
Didn't think cells would apply. Maybe I should be more patient but was in
hurry.

Thanks again,

EdC
(I'm too old to learn too many new tricks at the same time; 75 next week)


Debra Dalgleish said:
Something that may help you, as you record macros, is the Relative
Reference button on the Stop Recording toolbar. When activated, it
records movement, rather than specific cells.

If you have VBA help installed, you can select the word Cut in the code,
and press the F1 key. This will open help, at the page with information
about the Cut method. It explains that you can specify a destination for
the cut cells, as Don did.

His code is designed to work in column E, so if you want this to work in
the active column, you could add code to calculate that. For example:

'=================================
Sub CutValues()
Dim i As Integer
Dim r As Long
Dim col As Integer
r = ActiveCell.Row
col = ActiveCell.Column

If r < 3 Then
MsgBox "Please select a cell in row 3 or higher"
Exit Sub
End If

For i = 1 To 100
Cells(r, col).Cut (Cells(r - 2, col))
r = r + 5
Next i
End Sub
'===============================

Ed said:
Why does this work? Am I missing something? Where's the paste statement?

EdC
something like this. UNTESTED

for i=1 to 100 step 5
cells(i,"e").cut cells(i-2,"e")
next i

--
Don Guillett
SalesAid Software
(e-mail address removed)

Can I get help in this newsgroup with recording macros?

I have recorded the following macro using the macro recorder. The cursor

was

'in' cell E7

Selection.Cut
Range("E5").Select
ActiveSheet.Paste
Range("E10").Select

I would now like to convert this to a general purpose macro. I need help
using [R][C] notation.

The steps are simple:


For i = 1 to 100
(1) Using the active cell, cut its value (a constant)
(2) Move up 2 rows
(3) Paste value
(4) Move down 5 rows.
Next i

How do I change the recorded macro?

Thanks for help.

EdC
Click to expand...
Click to expand...
Click to expand...
 
Hi Ed,

Sometimes VBA Help isn't too helpful. It's a great resource if you know
what you're looking for, and need to find the details on how something
works. But, if you're not sure where to start, or what key words to
enter, it can be a daunting (or hopeless!) task.

And happy (almost) birthday! Keep learning those new tricks, a few at a
time.

Debra

Ed said:
Thank you very much, Debra!!!!

I looked all over for the way to record in relative reference. Used it in
Excel97 but couldn't find it in current XP version. Will look again.

I was totally disappointed with VBA help. Did just what you said; highlight
and F1. Worthless!!!! Didn't use cut but did try delete, selection, etc.
Didn't think cells would apply. Maybe I should be more patient but was in
hurry.

Thanks again,

EdC
(I'm too old to learn too many new tricks at the same time; 75 next week)


Something that may help you, as you record macros, is the Relative
Reference button on the Stop Recording toolbar. When activated, it
records movement, rather than specific cells.

If you have VBA help installed, you can select the word Cut in the code,
and press the F1 key. This will open help, at the page with information
about the Cut method. It explains that you can specify a destination for
the cut cells, as Don did.

His code is designed to work in column E, so if you want this to work in
the active column, you could add code to calculate that. For example:

'=================================
Sub CutValues()
Dim i As Integer
Dim r As Long
Dim col As Integer
r = ActiveCell.Row
col = ActiveCell.Column

If r < 3 Then
MsgBox "Please select a cell in row 3 or higher"
Exit Sub
End If

For i = 1 To 100
Cells(r, col).Cut (Cells(r - 2, col))
r = r + 5
Next i
End Sub
'===============================

Ed said:
Why does this work? Am I missing something? Where's the paste statement?

EdC


something like this. UNTESTED

for i=1 to 100 step 5
cells(i,"e").cut cells(i-2,"e")
next i

--
Don Guillett
SalesAid Software
(e-mail address removed)


Can I get help in this newsgroup with recording macros?

I have recorded the following macro using the macro recorder. The
Click to expand...
cursor
was


'in' cell E7

Selection.Cut
Range("E5").Select
ActiveSheet.Paste
Range("E10").Select

I would now like to convert this to a general purpose macro. I need
Click to expand...
help
using [R][C] notation.

The steps are simple:


For i = 1 to 100
(1) Using the active cell, cut its value (a constant)
(2) Move up 2 rows
(3) Paste value
(4) Move down 5 rows.
Next i

How do I change the recorded macro?

Thanks for help.

EdC
Click to expand...
Click to expand...
Click to expand...
 
Back
Top