Change a cell reference using VBA
Hello friends,
I had created a spreadsheet in Excel 2007 where there are many links which are interlinked with another spreadsheet, now what i want is that whenever i try to update or try to edit those links should be point to that file and should give me a call. When i asked my freinds about it they told me that this can be done only with macro and they don't have any idea about it. Does anyone know how to change a cell reference using VBA ?
Change a cell reference using VBA
Before going ahead i would like to ask you that have you created any macro before and are you using any HYPERLINK formula or are you using any menu to establish the hyper linking it, because on that bases i will provide you macro code.
How to change a cell reference using VBA
No I am using hyperlink "=[file_nameA]A1" and in this i want the filename to be changed each and everytime and at the same time cell reference would move along one column so it would then be "=[file_nameB]B1". I am planning to provide a user with a form where he needs to just select the file they want the data from and rest will be done by itself.
Change a cell reference using VBA
Hey Emmett i have created a code have a check for the same i am sure this will work for you.
Code:
Option Explicit
Option Base 1 ' Set default array subscripts to 1.
Sub Hyper_Linh_Change()
Dim My_INFO1 As Variant
Dim My_MONTH As String, My_FORMULA As String, New_FORMULA As String, StrMSG As String
Dim MONTH_List
Dim MyRG As Range, F As Object
Dim BEG_Char As Long, END_Char As Long
Set MyRG = Range("A1:A3")
MONTH_List = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sept", "Oct", "Nov", "Dec")
My_INFO1 = Application.InputBox("Enter a month number: 1, 2 ..12")
If (My_INFO1 <> False) Then
If (IsNumeric(My_INFO1)) Then
My_MONTH = MONTH_List(My_INFO1) & ".xls"
For Each F In MyRG
My_FORMULA = F.Formula
BEG_Char = InStr(My_FORMULA, "[")
END_Char = Len(My_FORMULA) - InStrRev(My_FORMULA, "]")
New_FORMULA = Left(My_FORMULA, BEG_Char - 1) & "[" & My_MONTH & "]" & Right(My_FORMULA, END_Char)
F.Formula = New_FORMULA
Next F
Else
StrMSG = "Answer" & My_MONTH & " is NOT a GOOD Selection"
MsgBox StrMSG, vbInformation, "ERROR"
End If
End If
End Sub
Change a cell reference using VBA
Thank you Ackley for replying me are you sure that the code which you had provided me is dealing with hyperlinks according to me simple cell reference not hyperlinks cell reference, can you correct it or is there anyone who know what changes are to be made in Ackley any help would be highly appreciated.
Change a cell reference using VBA
Don't worry Emmett i had made some changes in Ackley code and now i am sure it will work fine as you wanted to work
Code:
Sub Hyper_Link_Change2()
Dim My_INFO As Variant
Dim Old_MONTH As String, New_MONTH As String, My_FORMULA As String, StrMSG As String
Dim MONTH_List
Dim MyRG As Range
Dim BEG_Char As Long, END_Char As Long
Set MyRG = Range("A1:A3")
MONTH_List = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sept", "Oct", "Nov", "Dec")
My_INFO = Application.InputBox("Enter a month number: 1, 2 ..12")
If (My_INFO <> False) Then
If (IsNumeric(My_INFO)) Then
New_MONTH = MONTH_List(My_INFO) & ".xls"
My_FORMULA = MyRG.Cells(1, 1).Formula
BEG_Char = InStr(My_FORMULA, "[")
END_Char = InStrRev(My_FORMULA, "]")
Old_MONTH = Mid(My_FORMULA, BEG_Char + 1, END_Char - BEG_Char - 1)
MyRG.Replace What:=Old_MONTH, Replacement:=New_MONTH, LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Else
StrMSG = "Answer" & My_INFO & " is NOT a GOOD Selection"
MsgBox StrMSG, vbInformation, "ERROR"
End If
End If
End Sub