Syntax for relative R1C1 with variables?

[hannal]

In a Sub I have this code:

Application.Goto Reference:="myField"  Selection.FormulaR1C1 = "=RC[-3]+RC[-1]"

Note that myField is a column (in a database area, myField is the field name of the column) so the formula is calulated on all cells in the column, and for each cell in the column the arguments are from cells on the same row.

Now, instead of the fix values -3 and -1, I want to use variables
e.g.
column1 = -3
column2 = -1

What is the syntax?

I have tried several versions with "& variable &", for exampel:

Selection.FormulaR1C1 = "=RC["& column1 &"]+RC["& column2 &"]"

But it doesn't work.

Regards Hanna

 

[xlbo]

In what way does it not work - I just tested it with this:

column1 = -3
column2 = -1
Application.Goto Reference:="myField"
Selection.FormulaR1C1 = "=RC[" & column1 & "]+RC[" & column2 & "]"

and it entered the formula into all cells in a range I had designated as "myField"

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[SkipVought]

Hann,
It does work. I don't see the problem!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[hannal]

Hi!

Neither do I now....

I spent 5 hours trying to get the syntax to work and all the time I either got the error message:

'Run-time error 1004, Application-defined or Object-defined error'

when I was running my macro, or my code didn't even get get trough the debugger.

Since I'm new at this I thought that perhaps I'd misunderstood something.

Well now everything works just fine.
I'm sorry to have bothered you unnecessarily.
Thank you so much for taking your time to check the code! I am very grateful.

Best Regards / HL

 

[hannal]

For your information:

I found the error. There must be a space between the & and the variable e.g.

"& myField &" is OK
"&myField&" is WRONG

Amazing I didn't realize before...

Rgds HL