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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>>
>
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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
>>>
>>
>>
>
>