If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Formula Property - Referencing Strings Inside Formula Property Value
Would anyone know how to refer to a string inside the
formula property value? In my case I am checking a named cell to see if it is holding a specified string and if so want the cell formula to have the cell with the formula be blank, otherwise have a value of 2, as per below: Worksheets(1).Cells(23, 8).Formula _ = "=if(itemnmlnk1 "StockOut", "", 2)" However, though the formula works fine when inserted directly in the cell, the formla property returns An "Application-defined or object-defined error". I am thinking it is because I am using the string quotes inside the Formula property value quotes and the interpreter is having a problem with quotes inside of quotes? So I am thinking there must be some character to type in front of the quotes to indicate to the interpreter to allow the quotes to be used in the formula? Thank you much |
#2
|
|||
|
|||
Formula Property - Referencing Strings Inside Formula Property Value
From memory - when you want a quote " inside of other quotes " " you use
""" (3 quotes) so Worksheets(1).Cells(23, 8).Formula _ = "=if(itemnmlnk1 "StockOut", "", 2)" should become Worksheets(1).Cells(23, 8).Formula _ = "=if(itemnmlnk1 """StockOut""", """, 2)" --- Message posted from http://www.ExcelForum.com/ |
#3
|
|||
|
|||
Formula Property - Referencing Strings Inside Formula Property Value
Kieran,
I think it is just 2 quotes, the 3 comes in if it immediately follows a starting string quote or precedes a finishing string quote (and 4 if you want double quotes). So it should read Worksheets(1).Cells(23, 8).Formula _ = "=if(itemnmlnk1 ""StockOut"", """", 2)" -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Kieran " wrote in message ... From memory - when you want a quote " inside of other quotes " " you use """ (3 quotes) so Worksheets(1).Cells(23, 8).Formula _ = "=if(itemnmlnk1 "StockOut", "", 2)" should become Worksheets(1).Cells(23, 8).Formula _ = "=if(itemnmlnk1 """StockOut""", """, 2)" --- Message posted from http://www.ExcelForum.com/ |
#4
|
|||
|
|||
Formula Property - Referencing Strings Inside Formula Property Value
Oak,
Is this what you want For rw = 1 To 10 With .Worksheets("InvoiceForm") .Cells(rw, 8).FormulaR1C1 = "=if(R" & rw & "C4 """", 1, """")" End With Next rw -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Oak" wrote in message ... Thank you. The double quotes worked well. However, I am still having a problem with the Formula property now not transferring a variable's value, but instead just the variable, as all that is between the outter quotations is inserted into the cell quite literally: For rw = 1 to 10 With .Worksheets("InvoiceForm") .Cells(rw, 8).Formula = "=if(cells(rw,4) """", 1, """")" End With next rw The literal reference to rw gets inserted into each cell instead of its value. Would anyone know what can be done to have the incremented value inserted as each new cell is referenced? |
#5
|
|||
|
|||
Formula Property - Referencing Strings Inside Formula Property Value
Oak,
Try this then For rw = 1 to 10 With .Worksheets("InvoiceForm") .Cells(rw, 9).Formula = "=IF($H" & rw & """,VLOOKUP($H" & rw & ",ProdTable,4,FALSE),"")" End With next rw -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Oak" wrote in message ... Thank you. It is helpful + worked in the previous type situation, but is there something for when replacing a general variable. The application is a situation where I want to substitute a variable for the row in the $H20 address so it would address the same column cell in each next row as the For loop incremented, but yet when inserted in the cell would look like and retain the traditional Excel A1 cell type addressing, including the absolute cell addressing, so as to allow for autofill recognition and capability. For rw = 1 to 10 With .Worksheets("InvoiceForm") .Cells(rw, 9).Formula = "=IF($H20"",VLOOKUP($H20,ProdTable,4,FALSE), "")" End With next rw Something along the lines of ? .Cells(rw, 9).Formula = "=IF($H & rw"",VLOOKUP($H & rw,ProdTable,4,FALSE),"")" Thank you. |
#6
|
|||
|
|||
Formula Property - Referencing Strings Inside Formula Property Value
"=?Utf-8?B?T2Fr?=" wrote...
It is helpful + worked in the previous type situation, but is there something for when replacing a general variable. The application is a situation where I want to substitute a variable for the row in the $H20 address so it would address the same column cell in each next row as the For loop incremented, but yet when inserted in the cell would look like and retain the traditional Excel A1 cell type addressing, including the absolute cell addressing, so as to allow for autofill recognition and capability. For rw = 1 to 10 With .Worksheets("InvoiceForm") .Cells(rw, 9).Formula = "=IF($H20"",VLOOKUP($H20,ProdTable,4,FALSE), "")" End With next rw You have an implicit correspondence. When rw = 1, the corresponding col H row would be 20. When rw = 2, the corresponding col H row would be 21. That is, the col H row is 19 more than rw. Try Cells(rs, 9).Formula = "=IF($H" & (19 + rw) & _ """"",VLOOKUP($H" & (19 + rw) & ",ProdTable,4,FALSE),"""")" -- To top-post is human, to bottom-post and snip is sublime. |
#7
|
|||
|
|||
Formula Property - Referencing Strings Inside Formula Property Value
Thanks Tom. It worked quite well.
I tried to get back to you sooner, but I couldn't get back into this thread - after clicking it would just hang. ----- Bob Phillips wrote: ----- Oak, Try this then For rw = 1 to 10 With .Worksheets("InvoiceForm") .Cells(rw, 9).Formula = "=IF($H" & rw & """,VLOOKUP($H" & rw & ",ProdTable,4,FALSE),"")" End With next rw -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Oak" wrote in message ... Thank you. It is helpful + worked in the previous type situation, but is there something for when replacing a general variable. The application is a situation where I want to substitute a variable for the row in the $H20 address so it would address the same column cell in each next row as the For loop incremented, but yet when inserted in the cell would look like and retain the traditional Excel A1 cell type addressing, including the absolute cell addressing, so as to allow for autofill recognition and capability. For rw = 1 to 10 With .Worksheets("InvoiceForm") .Cells(rw, 9).Formula = "=IF($H20"",VLOOKUP($H20,ProdTable,4,FALSE), "")" End With next rw Something along the lines of ? .Cells(rw, 9).Formula = "=IF($H & rw"",VLOOKUP($H & rw,ProdTable,4,FALSE),"")" Thank you. |
#8
|
|||
|
|||
Formula Property - Referencing Strings Inside Formula Property Value
Thanks Harlan,
I had recognized that relationship as well and planned on and tried utilizing it by modifying the variable within the formula, but just couldn't get the formula to work until Tom clarified it. Tried to thank you sooner, but couldn't get into the thread correctly - it would just hang. |
#9
|
|||
|
|||
Formula Property - Referencing Strings Inside Formula Property Value
Don't know who Tom is but my name is Robert, familiarly known as Bob/
"Oak" wrote in message ... Thanks Tom. It worked quite well. I tried to get back to you sooner, but I couldn't get back into this thread - after clicking it would just hang. |
Thread Tools | |
Display Modes | |
|
|