A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Formula Property - Referencing Strings Inside Formula Property Value



 
 
Thread Tools Display Modes
  #1  
Old February 5th, 2004, 01:45 AM
Oak
external usenet poster
 
Posts: n/a
Default 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  
Old February 5th, 2004, 06:55 AM
Kieran
external usenet poster
 
Posts: n/a
Default 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  
Old February 5th, 2004, 10:28 AM
Bob Phillips
external usenet poster
 
Posts: n/a
Default 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  
Old February 5th, 2004, 07:30 PM
Bob Phillips
external usenet poster
 
Posts: n/a
Default 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  
Old February 5th, 2004, 09:28 PM
Bob Phillips
external usenet poster
 
Posts: n/a
Default 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  
Old February 5th, 2004, 09:28 PM
Harlan Grove
external usenet poster
 
Posts: n/a
Default 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  
Old February 9th, 2004, 11:16 PM
Oak
external usenet poster
 
Posts: n/a
Default 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  
Old February 9th, 2004, 11:26 PM
Oak
external usenet poster
 
Posts: n/a
Default 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  
Old February 10th, 2004, 11:54 AM
Bob Phillips
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:55 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.