Results 1 to 6 of 6

Thread: Change a cell reference using VBA

  1. #1
    Join Date
    Aug 2009
    Posts
    14

    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 ?

  2. #2
    Join Date
    Dec 2008
    Posts
    183

    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.

  3. #3
    Join Date
    Aug 2009
    Posts
    14

    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.

  4. #4
    Join Date
    Dec 2008
    Posts
    183

    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

  5. #5
    Join Date
    Aug 2009
    Posts
    14

    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.

  6. #6
    Join Date
    Jan 2006
    Posts
    211

    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

Similar Threads

  1. Replies: 2
    Last Post: 23-02-2012, 06:23 PM
  2. How to avoid cell reference changes after sorting in Excel
    By Wisaal in forum MS Office Support
    Replies: 2
    Last Post: 09-02-2012, 06:23 PM
  3. insert picture in excel 2007 based on cell reference
    By joe.polkendare in forum Windows Software
    Replies: 5
    Last Post: 25-07-2011, 11:40 PM
  4. Cell Reference in Excel
    By kamina23 in forum Windows Software
    Replies: 4
    Last Post: 07-10-2009, 11:53 PM
  5. Comparison of dates if reference cell is empty
    By cobra2008 in forum Windows Software
    Replies: 3
    Last Post: 23-09-2009, 11:22 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •