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.



--
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.

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



--
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.

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

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



--
D.S.

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

D.S. wrote in message
...
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

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.

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

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



--
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"")))"

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

D.S. wrote in message
...
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


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.

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

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



--
D.S.

You might want to use: C2:C6


"D.S." wrote:

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"")))"

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

D.S. wrote in message
...
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


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.

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

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



--
D.S.









--

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.

Dave Peterson wrote:

You might want to use: C2:C6

"D.S." wrote:

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"")))"

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

D.S. wrote in message
...
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


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.

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

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



--
D.S.









--

Dave Peterson


--

Dave Peterson

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


D.S. wrote in message
...
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"")))"

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

D.S. wrote in message
...
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



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.

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

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



--
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.


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


D.S. wrote in message
...
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"")))"

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

D.S. wrote in message
...
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




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.

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

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



--
D.S.

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

Best wishes Harald
Followup to newsgroup only please

"Tom Ogilvy" skrev i melding
...
you need to double up on your double quotes embedded in the string