Relative Cell Reference Resolution Issue
 

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

Relative Cell Reference Resolution Issue
 

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

--
Regards,
Tom Ogilvy


"needyourhelp" wrote in message
...
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

Relative Cell Reference Resolution Issue
 

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


"needyourhelp" wrote:

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

Relative Cell Reference Resolution Issue
 

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" wrote:

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

--
Regards,
Tom Ogilvy


"needyourhelp" wrote in message
...
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

Relative Cell Reference Resolution Issue
 

It should if the original formula is entered as relative.

It should work the same way as if entered manually.

--
Regards,
Tom Ogilvy

"needyourhelp" wrote in message
...
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


"needyourhelp" wrote:

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

Relative Cell Reference Resolution Issue
 

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

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

Why the single ticks ?

thanks,

tim

"Tom Ogilvy" wrote:

It should if the original formula is entered as relative.

It should work the same way as if entered manually.

--
Regards,
Tom Ogilvy

"needyourhelp" wrote in message
...
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


"needyourhelp" wrote:

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

Relative Cell Reference Resolution Issue
 

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

--
Regards,
Tom Ogilvy

"needyourhelp" wrote in message
...
I entered = "=Right(A2,5)"

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

Why the single ticks ?

thanks,

tim

"Tom Ogilvy" wrote:

It should if the original formula is entered as relative.

It should work the same way as if entered manually.

--
Regards,
Tom Ogilvy

"needyourhelp" wrote in message
...
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


"needyourhelp" wrote:

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