Relative Cell Reference Resolution Issue

[Guest]

As I'm new to VBA, and not really a programmer, I think this is a simple
syntax issue...

I would like to use a macro to autofill N-Rows of Column B with the formula
found in Cell B2. The values supplied to the formula should come from the
corrosponding Column A cell.

Here is my attempt...

LastCustomerInvoiceNumber = Cells(Rows.Count, "A").End(xlUp).Row
Range("B2").Select
ActiveCell.FormulaR1C1 = "=Right(A2,5)"
Selection.AutoFill Selection.Resize( _
rowsize:=LastCustomerInvoiceNumber), xlFillDefault

This code allways resolves to =Right(A2,5) for each N-Row B Cell

I started with...

ActiveCell.FormulaR1C1 = "=Right($A$2,5)"

which resulted in "... Object Method error 1004..."

Then I tried

ActiveCell.FormulaR1C1 = "=Right('$A$2',5)"

results in "... Some other Class or Method error...."

Then I tried

ActiveCell.FormulaR1C1 = "=Right(=$A$2,5)"

and

ActiveCell.FormulaR1C1 = "=Right(='$A$2',5)"

and

ActiveCell.FormulaR1C1 = "=Right(="$A$",5)"

all results in "... some Compiler syntax error or some Method ..."

I'm stumped.

It appears I'm stuck with a hard coded cell reference.

What do I need to do force VBA to resolve the relative cell address ???

thanks in advance,

tim

 

[Tom Ogilvy]

LastCustomerInvoiceNumber = _
Cells(Rows.Count, "A").End(xlUp).Row
Range("B2").Resize(lastCustomerInvoiceNumber - 1 _
,1).Formula = "=Right(A2,5)"

 

[Guest]

My Bad...

Forget the $A$2 syntax as that is exactly backwards of what I want...

Why doesn't the fill command use relative cell addressing for each new row ?

thanks,

tim

 

[Guest]

Thanks very much Tom.

It works like very well.

Why doesn't the fill command use relative cell addressing for each new row ?

thanks,

tim

 

[Tom Ogilvy]

It should if the original formula is entered as relative.

It should work the same way as if entered manually.

 

[Guest]

I entered = "=Right(A2,5)"

it kept resolving as =Right('A2',5)

Why the single ticks ?

thanks,

tim

 

[Tom Ogilvy]

Because you are using FormulaR1C1 rather than Formula

To demo from the immediate window:

ActiveCell.FormulaR1C1 = "=Left(A2,5)"
? activecell.Formula
=LEFT('A2',5)
Activecell.Formula = "=Left(A2,5)"
? activecell.Formula
=LEFT(A2,5)

Unfortunately, the macro recorder in later versions of Excel seems to record
in R1C1