Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



Change a cell reference using VBA

Software Development


Reply
 
Thread Tools Search this Thread
  #1  
Old 16-02-2010
Member
 
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 ?

Reply With Quote
  #2  
Old 16-02-2010
Member
 
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.
Reply With Quote
  #3  
Old 16-02-2010
Member
 
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.
Reply With Quote
  #4  
Old 16-02-2010
Member
 
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
Reply With Quote
  #5  
Old 16-02-2010
Member
 
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.
Reply With Quote
  #6  
Old 16-02-2010
Member
 
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
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Change a cell reference using VBA"
Thread Thread Starter Forum Replies Last Post
Want some help to create cell reference in Microsoft Excel LuniA MS Office Support 2 23-02-2012 07:23 PM
How to avoid cell reference changes after sorting in Excel Wisaal MS Office Support 2 09-02-2012 07:23 PM
insert picture in excel 2007 based on cell reference joe.polkendare Windows Software 5 26-07-2011 12:40 AM
Cell Reference in Excel kamina23 Windows Software 4 08-10-2009 12:53 AM
Comparison of dates if reference cell is empty cobra2008 Windows Software 3 24-09-2009 12:22 AM


All times are GMT +5.5. The time now is 05:39 AM.