Changing Columns Reference Code??

[stephen.smith]

Hey guys
I am trying to change a cell reference using VBA. The reference has the column with $ before hand so, $B and I am trying to use code to change this to any other column example $K on many different cells at one time. I have tried a replace code but its not working because of the $ I think. I need to keep the $ in the reference, so is there a way to do this. Any help would be really appreciated. Below is an example of what i have tried.
Thanks
Stephen

Code:

Sub test()
On Error Resume Next
Dim c As String, d As String
c = InputBox("Enter Original Column Letter")
d = InputBox("Enter New Column Letter")
Range("A1:A100").Replace "$" & c, "$" & d
End Sub

 

[jim may]

Using a simplier range and formula, your code worked for me. Provide us the cell content of a cell where it is not working.

 

[stephen.smith]

Thanks so much
My apologies the rang in my formula is B66:B391
This is the content of cell B331
='P:\IPS 201112\[Week By Week Risk Management Info.xlsx]Game Rate'!$B173
Thanks Again
Stephen

 

[jim may]

Sorry, but with your example I do not see the problem. If you run the code

Inputbox >> B
Inputbox >> J

Would not your given formula produce:

='P:\IPS 201112\[Week By Week Risk Management Info.xlsx]Game Rate'!$J173

 

[stephen.smith]

It should but it doesn't work.
Thanks

 

[jim may]

What is the content of the cell, AFTER running the code?

Staying with my example, is it (?):

='P:\IPS 201112\[Week By Week Risk Management Info.xlsx]Game Rate'!$J173

 

[stephen.smith]

It still says
='P:\IPS 201112\[Week By Week Risk Management Info.xlsx]Game Rate'!$B173
I really cant work this out

 

[stephen.smith]

Also Im not sure if it makes a difference or not but when I run this macro, a box appears on the screen with the letter B in it and a blank dialog box and displays ok or cancel buttons, it does the same following this with the letter L. Is this normal??

 

[stephen.smith]

Also just to let you know that the cell the formula is referring to is from another spreadsheet. Does this make a difference?

 

[jim may]

Range("A1:A100")<<< In your code instructs Excel to act upon the ActiveWorkbook AND the Activesheet, nothing else.. Substitute your Full Path Reference to the appropriate WB and Sheet!!