VBA Code HELP PLEASE

[stephen.smith]

Hi Guys
I am trying to use VBA code to find and replace a column number in a cell reference from column A to Column B, however in the cell reference $A has been used. I have tried to perform this using the code below but it doesnt work.
Code:
Sub test()
On Error Resume Next
Dim c As String, d As String
c = InputBox("A")
d = InputBox("B")
Range("A1:A100").Replace "$" & c, "$" & d
End Sub

I have also attached a picture of an example of what the cells look like and one of the cells show the exact cell reference of one of the cells I am attempting to change.
Any help would be really appreciated as this is causing me a lot of problems.
Thanks in advance
Stephen

Excel Workbook
	A	B	C	D	E	F	G
1	Column A
2	Column A
3	='C:\Users\Stephen\Desktop\[Test Database.xlsx]Sheet1'!$A4
4	Column A
5	Column A
6	Column A
7	Column A
8	Column A
9	Column A
10	Column A
11	Column A
12	Column A
13	Column A
14	Column A
15	Column A
16	Column A
Sheet2

 

[bensonsearch]

Hi,
Have you tried the find and replace function that is apart of excel?

ie hitting ctrl + F and going to replace section

 

[hiker95]

stephen.smith,


Sample formulae before the macro:

Excel Workbook
	A
1	#REF!
2	#REF!
3	#REF!
4	#REF!
5	#REF!
6	#REF!
7	#REF!
8	#REF!
9	#REF!
10	#REF!
11	#REF!
12	#REF!
13	#REF!
14	#REF!
15	#REF!
16	#REF!
Sheet2

After the macro:

Excel Workbook
	A
1	#REF!
2	#REF!
3	#REF!
4	#REF!
5	#REF!
6	#REF!
7	#REF!
8	#REF!
9	#REF!
10	#REF!
11	#REF!
12	#REF!
13	#REF!
14	#REF!
15	#REF!
16	#REF!
Sheet2

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub UpdateFormulae()
' hiker95, 09/15/2011
' http://www.mrexcel.com/forum/showthread.php?t=579219
Application.DisplayAlerts = False
Range("A1:A16").Replace What:="$A", Replacement:="$B", LookAt:=xlPart, _
  SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Application.DisplayAlerts = True
End Sub

Then run the UpdateFormulae macro.