FormulaR1C1

  • Thread starter Thread starter D.S.
  • Start date Start date
D

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.
 
When you include double quotes in your formula assignment, you have to double up
on them.

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



D.S. said:
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.
Click to expand...
 
And from your other thread, don't forget to change H2 to r1c1 notation.

Dave said:
When you include double quotes in your formula assignment, you have to double up
on them.

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

D.S. said:
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.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run Time Error, FormulaR1C1 9
Macro autofills formula when a cell value is changed 4
Why are the two codes below not getting the right formula for myworksheet event change formula? 2
Vlookup error 1
VLOOKUP with VB 1
macro to copy rows that contain a cell with a certain number 2
My Never ending ARRAY code problems 18
Copying Data Down the Column But Only Based on What's in Column A 1

Back
Top