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