changing active cells

Posted by nope on February 17, 2001 3:07 PM

I'm not getting this.

I have three columns with values in the first two. I'd like to write a macro that goes down the third column and pastes in a formula for as long as the first two have values.

I've figured out a good part of this - I think - but am having trouble with selecting the right cells.

Here it is so far:

Sub justgreat()

Dim MyCell As Range

For Each MyCell In Range("P6:P65536")
If MyCell >= 0 Then
'ActiveCell.Offset(0, 1).Activate
ActiveCell.FormulaR1C1 = "=RC[-1]*RC[1]"
End If
Next MyCell

End Sub

Suggestions are welcome, but I'd also like to really grasp the concepts in case I need something like this in the future. What/where is a good reference on how to make selections in Excel?

Thanks!

Posted by David Hawley on February 17, 2001 7:20 PM

Hi nope

You shouldn't use a loop for this, in fact I avoid loops whenever I can as they are far too slow! All you need to do is find out the last row number of your entries in Column P. This can be done by coming up from the very last cell in Column P (P65536) using End(xlup). Then as your formulas are relative one you can insert them in one go. Like this

Sub justgreat()
Dim StopRow As Long

StopRow = Range("P65536").End(xlUp).Row
Range("P6:P" & StopRow) = "=RC[-1]*RC[1]"

End Sub

Notice I have declared the variable "StopRow" as a Long, this is a good habit when dealing with Rows as there are 65536 of them. If you declared StopRow as an Integer you may get a "OverFlow: error as an Integer can only go to 32,767 where as a Long can go up to 2,147,483,647.

Also try avoid selecting cells when coding as this too can slow down the running of the code. Nine times out of ten selecting can be avoided.

Take a look at my web site, I have some examples of VBA among other things.

Hope this helps

Dave

OzGrid Business Applications

Posted by none on February 18, 2001 8:13 AM

thanks! I wouldn't have found that out quickly.