Where am I going wrong? (losing the variable because of need for quotation marks)

[Mark Stephens]

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

 

[Bob Phillips]

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]

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]

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)