Simple Question, Please help!

i want to take a value of a merged cell and expand that same value to each adjacent cell.

how do i get the variable b into the ActiveCell.Formula command? Is this possible?


Columns("B:B").Select
Selection.Insert Shift:=xlToRight
For a = 1 To 11
b = -1
Range("B" & a).Select
ActiveCell.Formula = "=R[0]C[-1]"
Do While ActiveCell = 0
ActiveCell.Formula = "R[b]C[-1]" ' Here is my problem
b = b - 1
Loop

Pete welcome to the forum.

Have a look at:


Range("B" & a).Formula = "=R[0]C[-1]"


Also have a look at the Offset property.

Hope that gets you started.

TJ

A few things here: First, you'll want to change ActiveCell.Formula to ActiveCell.FormulaR1C1, since you'er using R1C1 notation (e.g. R[0]C[-1] instead of "A14").

Second, FormulaR1C1 = "=R[" & b & "]C[-1]" is the proper way to include a variable in a string formula like that (don't forget the equal sign).

Lastly, you don't need to select the cells in order to put formulas into them.


Dim myCell As Range

Columns("B:B").Select
Selection.Insert Shift:=xlToRight
For a = 1 To 11
b = 0
'Cells(a, 2) refers to row a of column 2 (so if a = 1, then it refers to B1) and so on
Set myCell = Cells(a , 2)

Do While myCell = 0
myCell.FormulaR1C1 = "=R[0]C[-1]"
ActiveCell.FormulaR1C1 = "=R[" & b & "]C[-1]"
b = b - 1
Loop
Next a