Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
FormulaR1C1
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
FormulaR1C1
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." wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
FormulaR1C1
And from your other thread, don't forget to change H2 to r1c1 notation.
Dave Peterson wrote: 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." wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ActiveCell.FormulaR1C1 | Excel Discussion (Misc queries) | |||
reference to other worksheets in FormulaR1C1 | Excel Discussion (Misc queries) | |||
FormulaR1C1 | Excel Programming | |||
FormulaR1C1 | Excel Programming | |||
ActiveCell.FormulaR1C1 | Excel Programming |