Relative cell position in VBA

[iashorty]

When I am writing a formula in VBA how do I refer to a cell relatively rather
than an exact cell?

For example:
Range("B1").Select
Selection.End (xlDown)
ActiveCell.Offset(1, 7).Select

This puts me in cell in column "I" but the row is unknown to me. Now I want
to write a formula that adds the numbers in columns B-D and subtracts the
number in column J of the same line into column I. How do I reference cells
on the same row but in different columns when I do not know the row number?
And, can this be adjusted if I know the row number but the column is relative?

 

[Luke M]

Maybe something like
ActiveCell.FormulaR1C1 = "=R[-1]C[-1]"

Where you use the numbers in brackets to offset from current cell. If you're
already in the row/column you want, leave out those brackets, ie.
ActiveCell.FormulaR1C1 = "=R[-1]C"

 

[Rick Rothstein]

Leave it out and VB will use the current row. So, change your last line to
this (note the comma)...

ActiveCell.Offset(,N).Select

where N is the number of columns you want to offset from the current row. I
would like to point out though that you do not need to select a cell in
order to operate on it or its content in VB. Your 3 lines of posted code can
be reduced to this single line of code...

Range("B1").End(xlDown).Offset(1, 7).Select

and if you are selecting this cell to do something with it, that operation
can probably be combined into this single line of code as well. Perhaps this
previous posting of mine (a response to another person using
Select/Selection type constructions) will be of some help to you in your
future programming...

Whenever you see code constructed like this...

Range("A1").Select
Selection.<whatever>

you can almost always do this instead...

Range("A1").<whatever>

In your particular case, you have this...

Range("C2:C8193").Select 'select cells to export
For Each r In Selection.Rows

which, using the above concept, can be reduced to this...

For Each r In Range("C2:C8193").Rows

Notice, all I have done is replace Selection with the range you Select(ed)
in the previous statement and eliminate the process of doing any
Select(ion)s. Stated another way, the Selection produced from
Range(...).Select is a range and, of course, Range(...) is a range... and,
in fact, they are the same range, so it doesn't matter which one you use.
The added benefit of not selecting ranges first is your active cell does not
change.