|
Run Time Error, 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. |
Run Time Error, FormulaR1C1
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. |
Run Time Error, FormulaR1C1
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. |
Run Time Error, FormulaR1C1
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. |
Run Time Error, FormulaR1C1
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. |
Run Time Error, FormulaR1C1
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 |
Run Time Error, FormulaR1C1
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. |
Run Time Error, FormulaR1C1
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. |
Run Time Error, FormulaR1C1
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 |
| All times are GMT +1. The time now is 10:40 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com