Formula Property - Referencing Strings Inside Formula Property Value

  • Thread starter Thread starter Oak
  • Start date Start date
O

Oak

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
 
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)"
 
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)
 
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 1
With .Worksheets("InvoiceForm"
.Cells(rw, 8).Formula = "=if(cells(rw,4) <> """", 1, """")
End Wit
next r

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
 
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 said:
Thank you. The double quotes worked well.
However, I am still having a problem with the Formula property now not
Click to expand...
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
Click to expand...
as each new cell is referenced?
 
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 1
With .Worksheets("InvoiceForm"
.Cells(rw, 9).Formula = "=IF($H20<>"",VLOOKUP($H20,ProdTable,4,FALSE),"")
End Wit
next r

Something along the lines of
..Cells(rw, 9).Formula = "=IF($H & rw<>"",VLOOKUP($H & rw,ProdTable,4,FALSE),"")

Thank you.
 
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 said:
Thank you.
It is helpful + worked in the previous type situation, but is there
Click to expand...
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.
 
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
Click to expand...

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),"""")"
 
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
 
Don't know who Tom is but my name is Robert, familiarly known as Bob/

Oak said:
Thanks Tom. It worked quite well.
I tried to get back to you sooner, but I couldn't get back into this
Click to expand...
thread - after clicking it would just hang.
 
Back
Top