Code to write formula with a cell reference

[qwertty]

I have a worksheet with hundreds of Bloomberg formulas in it that I need to change regularly. I only need to change the date information, everything else stays the same (see the formula below). What I'd like to do is enter a new date in a cell and run a macro to change all the previous dates to this new date. A simple Find & Replace macro won't work because I need to update the formulas with dates from the prior two weeks (they overlap eachother using find/replace).

Here's one of the formulas I need to change:


=BDH("CL1 Comdty","PX_LAST","6/24/2011","6/24/2011","Dir=V","Dts=H","Sort=A","Quote=C","QtTyp=P","Days=A","Per=cd","DtFmt=D","Fill=P","UseDPDF=Y")

Basically, I want the macro to write the formula as you see it, but with a reference to a cell with the new date (e.g., change "6/24/2011" to some new date). Bloomberg requires the formula to appear EXACTLY as above.

I want to do something similiar to what I found in this thread but I'm having trouble getting it to work: http://www.mrexcel.com/forum/showthread.php?t=64290

I'm a relative newb with VBA. PLEASE HELP!

 

[Ralajer]

I don't have access to the function so I can't do any testing.

I am assuming you have tried using cell referernce instead of the date typed out and it didn't work.

=BDH("CL1 Comdty","PX_LAST",A1,A2,"Dir=V","Dts=H","Sort=A","Quote=C","QtTyp=P","Days=A","Per=cd","DtFmt=D","Fill=P","UseDPDF=Y")

Where A1 is the start date and A2 is the end date.

Try wrapping the cell reference in the TEXT function, that is if you haven't already. I think it should work as the TEXT function returns a string and not the serial date. I suspect the BDH function requires that dates be passed as strings so this might just solve the problem without a macro.

TEXT($A$1,"mm/dd/yyyy")
TEXT($A$2,"mm/dd/yyyy")

=BDH("CL1 Comdty","PX_LAST",TEXT($A$1,"mm/dd/yyyy"),TEXT($A$2,"mm/dd/yyyy"),"Dir=V","Dts=H","Sort=A","Quote=C","QtTyp=P","Days=A","Per=cd","DtFmt=D","Fill=P","UseDPDF=Y")

Let me know if the above works.