VBA Code Problem

[stephen.smith]

Hi Guys
I have a worksheet which has about 250 cells which have links to another excel spreadsheet. Cell B10 will =OtherSpreadsheet$D2, Cell B11 will =OtherSpreadsheet$D3, etc. however on a weekly basis I may need to change the column the info is coming from in the OtherSpreadsheet. So one any given week I may need to change all values that say $D to $K or any other specific column. At the moment I am using a find and replace however I would prefer to use VBA to do this for me if possible so it can happen at the click of a button. Another issue I have is that there may be other values on the page which have $D in there link which I dont want changed so the VBA code must be specific to the cells which are adjusted weekly. Can anyone help with how to write code for this problem.
Thanks
Stephen

 

[Scott Huish]

Why not just modify the formula to reference a worksheet cell where the data should come from?

There should also be a ! between the cell reference and the sheet name.

 

[stephen.smith]

Hi thanks for the reply,
I want it to be performed easily at the click of a button. I have 100 cells which need to be changed with one rule, then another 100 that need to be changed with another rule. I then have 250 cells that need to be changed with another separate rule, then 150 cells which need to be changed with another and finally another 150 that need to be changed with another rule. The find and replace takes me about 5-10 minutes however I really would love to set up a piece of code that would allow this to occur instantaneously.
Thanks
Stephen

 

[Scott Huish]

Basically:

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

 

[stephen.smith]

Thanks so much,
Thats really helpful
I really appreciate it.

 

[stephen.smith]

Hey, I have tried this code and unfortunately it doesnt work. I have tried changing the values that it looks for and the code works but when I add in the find "$" & c, it doesnt work. I think it is having issues with the $B, can anyone help.
Thanks
Stephen