R1C1 with macros

  • Thread starter Thread starter Russ
  • Start date Start date
R

Russ

I'm trying to define a relative range of cells in a macro and copy the
formulas from that range to the next row below using R1C1 cell
addressing style. I can't seem to get the the thing to go. Anyone
know how?

Russ
 
Russ

I'm not really understanding what you want to do but if, for example you
want to copy a relative formula, say in A13 to A1000 then you could use the
line

Range("A13:A1000").FillDown

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
To me that looks like an absolute address. What I want to do with my
spreadsheet is:

1. Find a certain cell I've entered "END" in.
2. Move up two rows and two columns to the right
3. Select some cells in that row.
4. Copy the formulas in those cells to the row below them.
5. Move the "END" one row lower.

This is all being done to avoid seeing the #DIV! message in some
formula cells that will not display a number until other cells in
their formula have data.

Russ
 
Russ

The reference you save is a range reference but in a formula it would be
relative. Now I understand your problem more and if all you are trying to
do is avoid DIV/0 errors, you can use an IF function. To use this for blank
cells in say column A enter (say in column C)

=IF(A1="","",B1/A1)

and copy down

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Russ

The reference you save is a range reference but in a formula it would be
relative. Now I understand your problem more and if all you are trying to
do is avoid DIV/0 errors, you can use an IF function. To use this for blank
cells in say column A enter (say in column C)

=IF(A1="","",B1/A1)

and copy down
Click to expand...

Okay, but if A1=0, you will still get the #DIV/0 error. 0 and "" are
not the same.

A better way would be to test for the error:

=IF(ISERR(B1/A1),"",B1/A1).


Mike Argy
Custom Office Solutions
and Windows/UNIX applications

Please post on-topic responses to the newsgroup

To e-mail me, remove nospam from the address in the headers
 
Mike A said:
Okay, but if A1=0, you will still get the #DIV/0 error. 0 and "" are
not the same.

A better way would be to test for the error:

=IF(ISERR(B1/A1),"",B1/A1).
Click to expand...

I disagree, no need to run an extra function, if you change the formula to

=IF(A1=0,"",B1/A1)

then it will work for blank cells as well

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Thanks for the suggestions folks. I put Peo's in a cell and it worked
well, so I went no further. I also got rid of the R1C1 style.

Russ
 
Mike/Peo

Old habits die hard.... The OP had specifically said that he wanted to
avoid showing the error before data was entered (Blank) so I have always
used "". Liked yours though (Peo) and I think we can all agree there is a
myriad of ways of achieving this. ISERR, ISERROR, IF(OR(....)), etc.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
I disagree, no need to run an extra function, if you change the formula to

=IF(A1=0,"",B1/A1)

then it will work for blank cells as well
Click to expand...

I did not know a test for 0 returns true on a blank cell. I have
always used ISERR()!!

Thanks, Peo!


Mike Argy
Custom Office Solutions
and Windows/UNIX applications

Please post on-topic responses to the newsgroup

To e-mail me, remove nospam from the address in the headers
 
Back
Top