Simultaneously edit range with different values

[nicolehalliday]

Hello

I am trying to edit a long range of cells that are actually the tickers for the 500 companies in the S&P500 index. They look like this:

A UN Equity
AA UN Equity
AAPL UW Equity
ABC UN Equity

...etc. What I would like them to be changed to is:

=BDH("A UN Equity","PX_HIGH",A1,A1)
=BDH("AA UN Equity","PX_HIGH",A1,A1)
=BDH("AAPL UW Equity","PX_HIGH",A1,A1)
=BDH("ABC UN Equity","PX_HIGH",A1,A1)

So basically I'm wondering if it's possible to edit before and after different values in a range if what you are adding is the same for every cell. I've tried selecting the range, making the changes and then CTRL+ENTER (and many other variations) with no luck. I dread the notion of editing each of the 500 cells manually so any ideas would be much appreciated!

 

[VoG]

Where are your values?You will need a macro.

 

[nicolehalliday]

Thanks for the reply. in A1 is =TODAY()-1 and the ticker names are in the range B1:B500. I thought I might need a macro but I wouldn't even know where to start. I am not very familiar with VBA, so I would really appreciate if you have any ideas!