Don't understand why this formula is not working - Help Please!
 

Hi,

I have a weird issue. I have a sheet that looks at a column to pull the
variable "notepad". I have no issue with that. It also goes one column
over to pull the row number on my summary sheet - which is called "xxxx".

The problem is this - the column on the formula I am trying to link is
"L" - 5 columns over from "G". The myFormula variable is the row
related to my notepad on the summary page - but if I use the correct row
- it will be 11 rows greater in the formula than what it should be.

I can make this work by just subtracting 11 from myFormula - but I just
want to know why it it references the "g" and the 11 in the range where
I want the formula to go.

Can anybody help? I find this very strange.


Sheets(notePad).Range("g11").Formula = "=xxxx!R[" & myFormula & "]c[5]"

Thanks much in advance,
Anita

Don't understand why this formula is not working - Help Please!
 

Since you're giving a formula in R1C1 reference style, you should use
..formulaR1C1 instead of .formula.

Sheets(notePad).Range("g11").FormulaR1C1 = "=xxxx!R[" & myFormula & "]c[5]"

a wrote:

Hi,

I have a weird issue. I have a sheet that looks at a column to pull the
variable "notepad". I have no issue with that. It also goes one column
over to pull the row number on my summary sheet - which is called "xxxx".

The problem is this - the column on the formula I am trying to link is
"L" - 5 columns over from "G". The myFormula variable is the row
related to my notepad on the summary page - but if I use the correct row
- it will be 11 rows greater in the formula than what it should be.

I can make this work by just subtracting 11 from myFormula - but I just
want to know why it it references the "g" and the 11 in the range where
I want the formula to go.

Can anybody help? I find this very strange.

Sheets(notePad).Range("g11").Formula = "=xxxx!R[" & myFormula & "]c[5]"

Thanks much in advance,
Anita

--

Dave Peterson

Don't understand why this formula is not working - Help Please!
 

Thanks, Dave, but perhaps I'm misunderstanding. I changed the formula
to this:


Sheets(notePad).Range("g11").FormulaR1C1 = "=xxxx!R[" & myFormula & "]c[5]"

But it is still working exactly as I explained in my first post. Is
there something different that I should be doing? As I said, I can
subtract the 11 - but it makes the code much harder to understand.

Thanks again,
Anita


Dave Peterson wrote:

Since you're giving a formula in R1C1 reference style, you should use
.formulaR1C1 instead of .formula.

Sheets(notePad).Range("g11").FormulaR1C1 = "=xxxx!R[" & myFormula & "]c[5]"

a wrote:

Hi,

I have a weird issue. I have a sheet that looks at a column to pull the
variable "notepad". I have no issue with that. It also goes one column
over to pull the row number on my summary sheet - which is called "xxxx".

The problem is this - the column on the formula I am trying to link is
"L" - 5 columns over from "G". The myFormula variable is the row
related to my notepad on the summary page - but if I use the correct row
- it will be 11 rows greater in the formula than what it should be.

I can make this work by just subtracting 11 from myFormula - but I just
want to know why it it references the "g" and the 11 in the range where
I want the formula to go.

Can anybody help? I find this very strange.

Sheets(notePad).Range("g11").Formula = "=xxxx!R[" & myFormula & "]c[5]"

Thanks much in advance,
Anita

Don't understand why this formula is not working - Help Please!
 

I thought that your code didn't put the correct formula in the cell because you
used r1c1 reference style.

But you're asking how R1C1 reference style works.

If you put =R[11]C[5]
into a cell (with R1C1 turned on), it means to go 11 rows down and 5 columns to
the right of the cell containing the formula.

Since you're putting the formula in G11, then it'll be myformula rows down and 5
columns to the right from your G11 cell.

(I'd still use .formular1c1 in the VBA code, though.)

a wrote:

Thanks, Dave, but perhaps I'm misunderstanding. I changed the formula
to this:

Sheets(notePad).Range("g11").FormulaR1C1 = "=xxxx!R[" & myFormula & "]c[5]"

But it is still working exactly as I explained in my first post. Is
there something different that I should be doing? As I said, I can
subtract the 11 - but it makes the code much harder to understand.

Thanks again,
Anita

Dave Peterson wrote:

Since you're giving a formula in R1C1 reference style, you should use
.formulaR1C1 instead of .formula.

Sheets(notePad).Range("g11").FormulaR1C1 = "=xxxx!R[" & myFormula & "]c[5]"

a wrote:

Hi,

I have a weird issue. I have a sheet that looks at a column to pull the
variable "notepad". I have no issue with that. It also goes one column
over to pull the row number on my summary sheet - which is called "xxxx".

The problem is this - the column on the formula I am trying to link is
"L" - 5 columns over from "G". The myFormula variable is the row
related to my notepad on the summary page - but if I use the correct row
- it will be 11 rows greater in the formula than what it should be.

I can make this work by just subtracting 11 from myFormula - but I just
want to know why it it references the "g" and the 11 in the range where
I want the formula to go.

Can anybody help? I find this very strange.

Sheets(notePad).Range("g11").Formula = "=xxxx!R[" & myFormula & "]c[5]"

Thanks much in advance,
Anita



--

Dave Peterson