Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Where am I going wrong? (losing the variable because of need for quotation marks)
Hi guys,
Nice to be back after many years in the wilderness:) I have a sub which places this formula in a cell: =BDH("stVar","PX Last","1/1/1900",blptoday()) stVar is a variable taken from a column of tickers in another sheet stepping through them one at a time. the issue I have is that the formula has to appear as above with the quatation marks and as soon as I start enclosing quatation marks in quotation marks the code gets totally confused (understandably). Does anyone know what to do in such circumstances, i am sure there is a well known workaround but I have been away from programming for a number of years now and am rather rusty, so any help will be much appreciated. Thanks in advance, Mark |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Where am I going wrong? (losing the variable because of need for quotation marks)
Activecell.Formula = "=BDH(""stVar"",""PX Last"",""1/1/1900"",blptoday())"
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Mark Stephens" wrote in message ... Hi guys, Nice to be back after many years in the wilderness:) I have a sub which places this formula in a cell: =BDH("stVar","PX Last","1/1/1900",blptoday()) stVar is a variable taken from a column of tickers in another sheet stepping through them one at a time. the issue I have is that the formula has to appear as above with the quatation marks and as soon as I start enclosing quatation marks in quotation marks the code gets totally confused (understandably). Does anyone know what to do in such circumstances, i am sure there is a well known workaround but I have been away from programming for a number of years now and am rather rusty, so any help will be much appreciated. Thanks in advance, Mark |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Where am I going wrong? (losing the variable because of need for quotation marks)
Hi Bob,
Thanks for that but there is one thing I see I didn't really make clear, and when I try the code and make the adjustment it is throwing up an error again, I get this in the cell: =BDH("stVar","PX Last","1/1/1900",blptoday()) What I want is this: stVar = StringValueOfstVar =BDH("StringValueOfstVar","PX Last","1/1/1900",blptoday()) I mentioned that stVar is taken from another cell value and so the notation: ""stVar"","" stops it from filling in the stVar with a variable (which then needs surrounding with quotation marks). I have tried a few things to get it to work but it still stubbornly refuses to do it (there seems to be a problem surrounding a comma with quatation marks too). BTW what does the .formula notation signify different from the .value? (obviously tells it to expect a formula but what does that mean in practical terms?). Thanks and regards, Mark "Bob Phillips" wrote in message ... Activecell.Formula = "=BDH(""stVar"",""PX Last"",""1/1/1900"",blptoday())" -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Mark Stephens" wrote in message ... Hi guys, Nice to be back after many years in the wilderness:) I have a sub which places this formula in a cell: =BDH("stVar","PX Last","1/1/1900",blptoday()) stVar is a variable taken from a column of tickers in another sheet stepping through them one at a time. the issue I have is that the formula has to appear as above with the quatation marks and as soon as I start enclosing quatation marks in quotation marks the code gets totally confused (understandably). Does anyone know what to do in such circumstances, i am sure there is a well known workaround but I have been away from programming for a number of years now and am rather rusty, so any help will be much appreciated. Thanks in advance, Mark |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Where am I going wrong? (losing the variable because of need for quotation marks)
Hi Mark,
OK, I think this is what you mean Dim strVar As String strVar = "StringValueOfstVar" ActiveCell.Formula = "=BDH(""" & strVar & """,""PX Last"",""1/1/1900"",blptoday())" You are right, Value is no different to Formula in this context, and you could use the Value property (I am sure I have come across an instance where it didn't work, but I cannot recall the detail), and the maco recorder uses Formula to set a value if I recall correctly. However, it comes more into its own using FormulaR1C1 where you can use numeric row and column notation rather than the numeric row, letter column of the A1 notation. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Mark Stephens" wrote in message ... Hi Bob, Thanks for that but there is one thing I see I didn't really make clear, and when I try the code and make the adjustment it is throwing up an error again, I get this in the cell: =BDH("stVar","PX Last","1/1/1900",blptoday()) What I want is this: stVar = StringValueOfstVar =BDH("StringValueOfstVar","PX Last","1/1/1900",blptoday()) I mentioned that stVar is taken from another cell value and so the notation: ""stVar"","" stops it from filling in the stVar with a variable (which then needs surrounding with quotation marks). I have tried a few things to get it to work but it still stubbornly refuses to do it (there seems to be a problem surrounding a comma with quatation marks too). BTW what does the .formula notation signify different from the .value? (obviously tells it to expect a formula but what does that mean in practical terms?). Thanks and regards, Mark "Bob Phillips" wrote in message ... Activecell.Formula = "=BDH(""stVar"",""PX Last"",""1/1/1900"",blptoday())" -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Mark Stephens" wrote in message ... Hi guys, Nice to be back after many years in the wilderness:) I have a sub which places this formula in a cell: =BDH("stVar","PX Last","1/1/1900",blptoday()) stVar is a variable taken from a column of tickers in another sheet stepping through them one at a time. the issue I have is that the formula has to appear as above with the quatation marks and as soon as I start enclosing quatation marks in quotation marks the code gets totally confused (understandably). Does anyone know what to do in such circumstances, i am sure there is a well known workaround but I have been away from programming for a number of years now and am rather rusty, so any help will be much appreciated. Thanks in advance, Mark |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
defining a variable containing quotation marks | Excel Programming | |||
Quotation Marks | Excel Discussion (Misc queries) | |||
quotation marks | Excel Worksheet Functions | |||
quotation marks | Excel Programming | |||
Using quotation marks | Excel Programming |