VBA entering a formula in a cell

[jrobin5881]

I'm trying to enter a formula in a cell using VBA. I've found code out there and have tried modifying it with no luck.

I have the active cell as N2 and in the cell I want a very simple formula =A2 which will display the value of A2 in cell N2. I want to increment the value of the formula by two as I move down the N column so in N3 I would have = A4, N4 would have = A6 etc.

I've tried modifying ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])" to read ActiveCell.FormulaR1C1 = "=A2" but am ending up with '=A2' in the cell. I plan on adding a counter to increment the row reference by two as it loops through. Below is the code without the incrementer built in yet

Do
ActiveCell.FormulaR1C1 = "=A2"
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 1))

 

[xlbo]

ActiveCell.Formula = "=A2"


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[PHV]

And what about this ?
ActiveCell.Formula = "=A2"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jrobin5881]

Wow are you guy's fast!!! Thanks worked like a charm as soon as I took out the RC reference. I know's it's simplistic but here's my code in case someone else has a similar issue:

Sub testFillColumn()
Dim intCount As Integer
intCount = 2
' This loop runs until there is nothing in the next column
Do
ActiveCell.Formula = "=A" & intCount
ActiveCell.Offset(1, 0).Select
intCount = intCount + 2
Loop Until IsEmpty(ActiveCell.Offset(0, 1))
End Sub

 

[xlbo]

no need to loop - how about this

LastRow = cells(rows.count,activecell.offset(0,1).column).end(xlup).row
Range(cells(activecell.row,activecell.column),cells(activecell.column,LastRow)).formula = "=A2"

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[PHV]

Geoff, seems you missed the following:

increment the row reference by two

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[xlbo]

ooops - was looking at the 1st post

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[firefytr]

How about ..

=INDIRECT("A"&ROW(A1)*2)

?

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci