Cell references in Macros

[Jean-Paul Hahn]

Hello

I want to record a macro that jumps to the last row of a range and inserts a
new row thus expanding the range (name).

Although I checked the R1C1 reference style in options; the macro is created
with fix row references.

Rows("7:7").Select
Selection.Insert Shift:=xlDown

Does any one have a tip how to bring the macro recorder to using relative
references

(e.g. ActiveCell.Offset(-2, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown)

without having to write the macro manually?

Best regards

Jean-Paul

 

[Bob Phillips]

The macro recorder will always pick up the cell reference as it is, not
relative (how would it know what to be relative to).

It is one of the disadvantages of the macro recorder, but it was never
intended as a full blow development environment, for that you have to cut
some of it yourself.

 

[David McRitchie]

Hi Jean-Paul,
You will only get an idea of what you might use from the macro recorder
not the exact code that you need. There is the hidden part on the
Stop Macro recording on the right that toggles Relative Reference.

Excel wants to insert before a reference row, is that what you want
or do you want it below the reference row. You can use OFFSET
as you have shown to modify.

Do you really need to expand the range. If you just want to copy
the formulas down to a new row see
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

To combine two ranges see AddRange on Chip Pearson's pages
http://www.cpearson.com/excel/colors.htm

To find the row of the lastcell in a selected range
rlong = selection.SpecialCells(xlLastCell).Row

you might want to use .entirerow to expand a single cell
to refer to the entire row.

 

[Jean-Paul Hahn]

Hi David,

Thank you very much. This is exactly what I wanted to do!

I want to expand the range, because I want to create a graph out of the data
series that gets automatically updated if I insert a new value. I works fine
now.

One more, thank you very much.

Jean-Paul

Hi Jean-Paul,
You will only get an idea of what you might use from the macro recorder
not the exact code that you need. There is the hidden part on the
Stop Macro recording on the right that toggles Relative Reference.

Excel wants to insert before a reference row, is that what you want
or do you want it below the reference row. You can use OFFSET
as you have shown to modify.

Do you really need to expand the range. If you just want to copy
the formulas down to a new row see
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

To combine two ranges see AddRange on Chip Pearson's pages
http://www.cpearson.com/excel/colors.htm

To find the row of the lastcell in a selected range
rlong = selection.SpecialCells(xlLastCell).Row

you might want to use .entirerow to expand a single cell
to refer to the entire row.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Hello

I want to record a macro that jumps to the last row of a range and
inserts a
new row thus expanding the range (name).

Although I checked the R1C1 reference style in options; the macro is
created
with fix row references.

Rows("7:7").Select
Selection.Insert Shift:=xlDown

Does any one have a tip how to bring the macro recorder to using relative
references

(e.g. ActiveCell.Offset(-2, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown)

without having to write the macro manually?

Best regards

Jean-Paul

 

[Jean-Paul Hahn]

Hi Bob,

According to David (see second posting) you can simply activate the relative
references by clicking the icon in the "Stop macro" window.

Best regards

Jean-Paul

 

[Bob Phillips]

Your example led me to believe that you were referring to some offset from a
relative point, not relative/absolute cell references. What is absolute
about

Rows("7:7").Select

and so what does

(e.g. ActiveCell.Offset(-2, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown)

have to do with your question?

 

[Curt]

made a macro with copy & paste need to get a way to insert code to check if
cell has a value befor it replaces.Have '0's in a column need to insert where
others have been deleted or removed as an update feature when error msg.

Hi Jean-Paul,
You will only get an idea of what you might use from the macro recorder
not the exact code that you need. There is the hidden part on the
Stop Macro recording on the right that toggles Relative Reference.

Excel wants to insert before a reference row, is that what you want
or do you want it below the reference row. You can use OFFSET
as you have shown to modify.

Do you really need to expand the range. If you just want to copy
the formulas down to a new row see
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

To combine two ranges see AddRange on Chip Pearson's pages
http://www.cpearson.com/excel/colors.htm

To find the row of the lastcell in a selected range
rlong = selection.SpecialCells(xlLastCell).Row

you might want to use .entirerow to expand a single cell
to refer to the entire row.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Hello

I want to record a macro that jumps to the last row of a range and inserts a
new row thus expanding the range (name).

Although I checked the R1C1 reference style in options; the macro is created
with fix row references.

Rows("7:7").Select
Selection.Insert Shift:=xlDown

Does any one have a tip how to bring the macro recorder to using relative
references

(e.g. ActiveCell.Offset(-2, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown)

without having to write the macro manually?

Best regards

Jean-Paul

 

[David McRitchie]

Hi Curt,
The original poster's question was answered to his satisfaction two months ago.
Glad you did not insert question into someone else's thread, but
you should start a new thread even if your question is similar given that the
thread is more than five days old. The correct group for macro code is
microsoft.public.excel.programming and you should have started a *new* thread.

I can't follow question even if the word "need" begins a new sentence in both occurrences.

It would help if you gave the overall purpose of the macro -- perhaps there is
already code to do what you want.

If you need to check if a cell has a value of 0 or not, I expect you are talking
about a loop, and you would need to individually check the cells in say a
column to see if it's value is 0. But I'm not even sure if it is zero you are
looking to replace or to avoid. You can use SpecialCells to select cells
that have numbers and then check if the cell has a value of zero.

And now I see at the end of the question error message, how does that
fit it, is it an error recovery that you are looking for?.