| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Changing relative cell reference | Excel Discussion (Misc queries) | |||
| Vlookup & relative cell reference? | Excel Worksheet Functions | |||
| Relative cell reference | Excel Discussion (Misc queries) | |||
| picture relative to cell reference | Excel Discussion (Misc queries) | |||
| Relative Cell Reference | Excel Worksheet Functions | |||
Hybrid Mode
