Run Time Error, FormulaR1C1

[D.S.]

I have two sheets in the same workbook. In the first sheet, I'm trying to
programmatically populate cells with formulae to insure the correct formula
is always in the cells. The cell is too the left of a MSQuery, and the
formulae are not copying down along with the query refresh. When the code
runs, I'm getting a <run time> error at this line.

Range("A2").FormulaR1C1 =
"=UPPER(IF(ISERROR(VLOOKUP(RC[8],JobNotes!C[2]:C[6],2,FALSE)),"",IF(VLOOKUP(
RC[8],JobNotes!C[2]:C[6],2,FALSE)<>"",VLOOKUP(H2,JobNotes!C[2]:C[6],2,FALSE)
,"")))"

I'm looking for the value found in the current sheet current row column "H",
to see if its in sheet <JobNotes> column array "B:F", and if found, return
the contents of the cell in the second column of the array.

 

[Harald Staff]

Hi Ds

"H2" is not a valid r1c1 address. Try replacing it with R2C8 and see if it helps.
-untested, sorry. It's a big scenario to set up for testing, that one.

 

[D.S.]

That did need corrected, but still getting the run time error. Present code
is as follows:

Range("A2").FormulaR1C1 =
"=UPPER(IF(ISERROR(VLOOKUP(R[2]C[8],JobNotes!C[2]:C[6],2,FALSE)),"",IF(VLOOK
UP(R[2]C[8],JobNotes!C[2]:C[6],2,FALSE)<>"",VLOOKUP(R[2]C[8],JobNotes!C[2]:C
[6],2,FALSE),"")))"

D.S.

Hi Ds

"H2" is not a valid r1c1 address. Try replacing it with R2C8 and see if it helps.
-untested, sorry. It's a big scenario to set up for testing, that one.

--
HTH. Best wishes Harald
Followup to newsgroup only please.

I have two sheets in the same workbook. In the first sheet, I'm trying to
programmatically populate cells with formulae to insure the correct formula
is always in the cells. The cell is too the left of a MSQuery, and the
formulae are not copying down along with the query refresh. When the code
runs, I'm getting a <run time> error at this line.

Range("A2").FormulaR1C1 =
"=UPPER(IF(ISERROR(VLOOKUP(RC[8],JobNotes!C[2]:C[6],2,FALSE)),"",IF(VLOOKUP(

,"")))"

I'm looking for the value found in the current sheet current row column "H",
to see if its in sheet <JobNotes> column array "B:F", and if found, return
the contents of the cell in the second column of the array.

 

[Tom Ogilvy]

you need to double up on your double quotes embedded in the string

Sub Tester1()
Dim sStr as String
sStr =
"=UPPER(IF(ISERROR(VLOOKUP(R[2]C[8],JobNotes!C[2]:C[6],2,FALSE)),"""",IF(VLO
OKUP(R[2]C[8],JobNotes!C[2]:C[6],2,FALSE)<>"""",VLOOKUP(R[2]C[8],JobNotes!C[
2]:C[6],2,FALSE),"""")))"
Range("A2").FormulaR1C1 = sStr

End Sub

At leasted entered successfully for me.

--
Regards,
Tom Ogilvy

That did need corrected, but still getting the run time error. Present code
is as follows:
Range("A2").FormulaR1C1 =
"=UPPER(IF(ISERROR(VLOOKUP(R[2]C[8],JobNotes!C[2]:C[6],2,FALSE)),"""",IF(VLO
OK

[6],2,FALSE),"""")))"

D.S.

Hi Ds

"H2" is not a valid r1c1 address. Try replacing it with R2C8 and see if

it

helps.
-untested, sorry. It's a big scenario to set up for testing, that one.

trying

to

"=UPPER(IF(ISERROR(VLOOKUP(RC[8],JobNotes!C[2]:C[6],2,FALSE)),"",IF(VLOOKUP(

,"")))"

I'm looking for the value found in the current sheet current row

column

 

[D.S.]

Thanks Tom, thought the quotes were doubled. I did change the colum
references though. This seems to give me the exact result I'm looking for,
but don't understand why I had to change my colum reference in the
'JobNotes' sheet to C[1]:C[5] to look at columns B:F, would think that
should be C[2]:C[6]

I also added the <No> in the double quotes, to give a more visual
confermation that the data was not found.

Range("A2").FormulaR1C1 =
"=UPPER(IF(ISERROR(VLOOKUP(RC[7],JobNotes!C[1]:C[5],2,FALSE)),""No"",IF(VLOO
KUP(RC[7],JobNotes!C[1]:C[5],2,FALSE)<>"""",VLOOKUP(RC[7],JobNotes!C[1]:C[5]
,2,FALSE),""No"")))"

you need to double up on your double quotes embedded in the string

Sub Tester1()
Dim sStr as String
sStr =
"=UPPER(IF(ISERROR(VLOOKUP(R[2]C[8],JobNotes!C[2]:C[6],2,FALSE)),"""",IF(VLO

2]:C[6],2,FALSE),"""")))"
Range("A2").FormulaR1C1 = sStr

End Sub

At leasted entered successfully for me.

--
Regards,
Tom Ogilvy

That did need corrected, but still getting the run time error. Present code
is as follows:
Range("A2").FormulaR1C1 =

"=UPPER(IF(ISERROR(VLOOKUP(R[2]C[8],JobNotes!C[2]:C[6],2,FALSE)),"""",IF(VLO

OK

:C
[6],2,FALSE),"""")))"

D.S.

Hi Ds

"H2" is not a valid r1c1 address. Try replacing it with R2C8 and see

if

it trying the
code

"=UPPER(IF(ISERROR(VLOOKUP(RC[8],JobNotes!C[2]:C[6],2,FALSE)),"",IF(VLOOKUP(

,"")))"

I'm looking for the value found in the current sheet current row

column

"H",
to see if its in sheet <JobNotes> column array "B:F", and if found, return
the contents of the cell in the second column of the array.

 

[Dave Peterson]

You might want to use: C2:C6

Thanks Tom, thought the quotes were doubled. I did change the colum
references though. This seems to give me the exact result I'm looking for,
but don't understand why I had to change my colum reference in the
'JobNotes' sheet to C[1]:C[5] to look at columns B:F, would think that
should be C[2]:C[6]

I also added the <No> in the double quotes, to give a more visual
confermation that the data was not found.

Range("A2").FormulaR1C1 =
"=UPPER(IF(ISERROR(VLOOKUP(RC[7],JobNotes!C[1]:C[5],2,FALSE)),""No"",IF(VLOO
KUP(RC[7],JobNotes!C[1]:C[5],2,FALSE)<>"""",VLOOKUP(RC[7],JobNotes!C[1]:C[5]
,2,FALSE),""No"")))"

you need to double up on your double quotes embedded in the string

Sub Tester1()
Dim sStr as String
sStr =
"=UPPER(IF(ISERROR(VLOOKUP(R[2]C[8],JobNotes!C[2]:C[6],2,FALSE)),"""",IF(VLO

2]:C[6],2,FALSE),"""")))"
Range("A2").FormulaR1C1 = sStr

End Sub

At leasted entered successfully for me.

--
Regards,
Tom Ogilvy

That did need corrected, but still getting the run time error. Present code
is as follows:
Range("A2").FormulaR1C1 =

"=UPPER(IF(ISERROR(VLOOKUP(R[2]C[8],JobNotes!C[2]:C[6],2,FALSE)),"""",IF(VLO

OK

:C
[6],2,FALSE),"""")))"

D.S.

Hi Ds

"H2" is not a valid r1c1 address. Try replacing it with R2C8 and see

if

it
helps.
-untested, sorry. It's a big scenario to set up for testing, that one.

--
HTH. Best wishes Harald
Followup to newsgroup only please.

I have two sheets in the same workbook. In the first sheet, I'm trying
to
programmatically populate cells with formulae to insure the correct
formula
is always in the cells. The cell is too the left of a MSQuery, and the
formulae are not copying down along with the query refresh. When the
code
runs, I'm getting a <run time> error at this line.

Range("A2").FormulaR1C1 =

"=UPPER(IF(ISERROR(VLOOKUP(RC[8],JobNotes!C[2]:C[6],2,FALSE)),"",IF(VLOOKUP(

,"")))"

I'm looking for the value found in the current sheet current row column
"H",
to see if its in sheet <JobNotes> column array "B:F", and if found,
return
the contents of the cell in the second column of the array.

 

[Dave Peterson]

I should have added that c[2]:c[6] are relative references. It'd doesn't always
represent columns B:F. It represents the column 2 (to 6) to the right of the
cell that holds the formula.

You might want to use: C2:C6

Thanks Tom, thought the quotes were doubled. I did change the colum
references though. This seems to give me the exact result I'm looking for,
but don't understand why I had to change my colum reference in the
'JobNotes' sheet to C[1]:C[5] to look at columns B:F, would think that
should be C[2]:C[6]

I also added the <No> in the double quotes, to give a more visual
confermation that the data was not found.

Range("A2").FormulaR1C1 =
"=UPPER(IF(ISERROR(VLOOKUP(RC[7],JobNotes!C[1]:C[5],2,FALSE)),""No"",IF(VLOO
KUP(RC[7],JobNotes!C[1]:C[5],2,FALSE)<>"""",VLOOKUP(RC[7],JobNotes!C[1]:C[5]
,2,FALSE),""No"")))"

you need to double up on your double quotes embedded in the string

Sub Tester1()
Dim sStr as String
sStr =
"=UPPER(IF(ISERROR(VLOOKUP(R[2]C[8],JobNotes!C[2]:C[6],2,FALSE)),"""",IF(VLO

2]:C[6],2,FALSE),"""")))"
Range("A2").FormulaR1C1 = sStr

End Sub

At leasted entered successfully for me.

--
Regards,
Tom Ogilvy

That did need corrected, but still getting the run time error. Present
code
is as follows:

Range("A2").FormulaR1C1 =

"=UPPER(IF(ISERROR(VLOOKUP(R[2]C[8],JobNotes!C[2]:C[6],2,FALSE)),"""",IF(VLO
OK

:C
[6],2,FALSE),"""")))"

D.S.

Hi Ds

"H2" is not a valid r1c1 address. Try replacing it with R2C8 and see if
it
helps.
-untested, sorry. It's a big scenario to set up for testing, that one.

--
HTH. Best wishes Harald
Followup to newsgroup only please.

I have two sheets in the same workbook. In the first sheet, I'm
trying
to
programmatically populate cells with formulae to insure the correct
formula
is always in the cells. The cell is too the left of a MSQuery, and the
formulae are not copying down along with the query refresh. When the
code
runs, I'm getting a <run time> error at this line.

Range("A2").FormulaR1C1 =


"=UPPER(IF(ISERROR(VLOOKUP(RC[8],JobNotes!C[2]:C[6],2,FALSE)),"",IF(VLOOKUP(

,"")))"

I'm looking for the value found in the current sheet current row
column
"H",
to see if its in sheet <JobNotes> column array "B:F", and if found,
return
the contents of the cell in the second column of the array.

 

[Tom Ogilvy]

Just to add:
Your using relative references - so you will get results based on whatever
the activecell is. Use absolute references as Dave suggests.

--
Regards,
Tom Ogilvy

Thanks Tom, thought the quotes were doubled. I did change the colum
references though. This seems to give me the exact result I'm looking for,
but don't understand why I had to change my colum reference in the
'JobNotes' sheet to C[1]:C[5] to look at columns B:F, would think that
should be C[2]:C[6]

I also added the <No> in the double quotes, to give a more visual
confermation that the data was not found.

Range("A2").FormulaR1C1 =
"=UPPER(IF(ISERROR(VLOOKUP(RC[7],JobNotes!C[1]:C[5],2,FALSE)),""No"",IF(VLOO

,2,FALSE),""No"")))"

you need to double up on your double quotes embedded in the string

Sub Tester1()
Dim sStr as String
sStr =

"=UPPER(IF(ISERROR(VLOOKUP(R[2]C[8],JobNotes!C[2]:C[6],2,FALSE)),"""",IF(VLO

2]:C[6],2,FALSE),"""")))"
Range("A2").FormulaR1C1 = sStr

End Sub

At leasted entered successfully for me.

--
Regards,
Tom Ogilvy

That did need corrected, but still getting the run time error.

Present
code

is as follows:
Range("A2").FormulaR1C1 =

"=UPPER(IF(ISERROR(VLOOKUP(R[2]C[8],JobNotes!C[2]:C[6],2,FALSE)),"""",IF(VLO

:C
[6],2,FALSE),"""")))"

D.S.

Hi Ds

"H2" is not a valid r1c1 address. Try replacing it with R2C8 and see

if

it
helps.
-untested, sorry. It's a big scenario to set up for testing, that one.

--
HTH. Best wishes Harald
Followup to newsgroup only please.

I have two sheets in the same workbook. In the first sheet, I'm trying
to
programmatically populate cells with formulae to insure the correct
formula
is always in the cells. The cell is too the left of a MSQuery, and the
formulae are not copying down along with the query refresh. When the
code
runs, I'm getting a <run time> error at this line.

Range("A2").FormulaR1C1 =

"=UPPER(IF(ISERROR(VLOOKUP(RC[8],JobNotes!C[2]:C[6],2,FALSE)),"",IF(VLOOKUP(

,"")))"

I'm looking for the value found in the current sheet current row column
"H",
to see if its in sheet <JobNotes> column array "B:F", and if found,
return
the contents of the cell in the second column of the array.

 

[D.S.]

Thanks to all, didn't dawn on me that it was a relative reference, I was
thinking it was an absolute reference. Lots of help, thanks again.

D.S.

Just to add:
Your using relative references - so you will get results based on whatever
the activecell is. Use absolute references as Dave suggests.

--
Regards,
Tom Ogilvy

Thanks Tom, thought the quotes were doubled. I did change the colum
references though. This seems to give me the exact result I'm looking for,
but don't understand why I had to change my colum reference in the
'JobNotes' sheet to C[1]:C[5] to look at columns B:F, would think that
should be C[2]:C[6]

I also added the <No> in the double quotes, to give a more visual
confermation that the data was not found.

Range("A2").FormulaR1C1 =

"=UPPER(IF(ISERROR(VLOOKUP(RC[7],JobNotes!C[1]:C[5],2,FALSE)),""No"",IF(VLOO

,2,FALSE),""No"")))"

you need to double up on your double quotes embedded in the string

Sub Tester1()
Dim sStr as String
sStr =

"=UPPER(IF(ISERROR(VLOOKUP(R[2]C[8],JobNotes!C[2]:C[6],2,FALSE)),"""",IF(VLO

2]:C[6],2,FALSE),"""")))"
Range("A2").FormulaR1C1 = sStr

End Sub

At leasted entered successfully for me.

--
Regards,
Tom Ogilvy

That did need corrected, but still getting the run time error. Present
code
is as follows:

Range("A2").FormulaR1C1 =

"=UPPER(IF(ISERROR(VLOOKUP(R[2]C[8],JobNotes!C[2]:C[6],2,FALSE)),"""",IF(VLO

:C
[6],2,FALSE),"""")))"

D.S.

Hi Ds

"H2" is not a valid r1c1 address. Try replacing it with R2C8 and

see
if

it
helps.
-untested, sorry. It's a big scenario to set up for testing, that one.

--
HTH. Best wishes Harald
Followup to newsgroup only please.

I have two sheets in the same workbook. In the first sheet, I'm
trying
to
programmatically populate cells with formulae to insure the correct
formula
is always in the cells. The cell is too the left of a MSQuery,

and
the

formulae are not copying down along with the query refresh.

When
the

code
runs, I'm getting a <run time> error at this line.

Range("A2").FormulaR1C1 =

"=UPPER(IF(ISERROR(VLOOKUP(RC[8],JobNotes!C[2]:C[6],2,FALSE)),"",IF(VLOOKUP(

,"")))"

I'm looking for the value found in the current sheet current row
column
"H",
to see if its in sheet <JobNotes> column array "B:F", and if found,
return
the contents of the cell in the second column of the array.

 

[Harald Staff]

Ouch. Should have spotted that one. Sorry, and thank you Tom.

Best wishes Harald
Followup to newsgroup only please