+ Reply to Thread
Results 1 to 11 of 11

VBA to change cell reference of formula within a given cell

  1. #1
    Registered User
    Join Date
    01-07-2014
    Location
    Bath, England
    MS-Off Ver
    Excel 2007
    Posts
    7

    VBA to change cell reference of formula within a given cell

    Afternoon all,
    Been lurking around the forums on and off for some time.

    Its been a while since i needed to do anything in VBA and as such I am very rusty but hoping you guys can help me out with something.

    I need to be able to loop through each cell of a selected range, and for each cell, i need to change any row values given in any/all formulas within the cell.
    The new row value will be an increment off a number given by the end user.
    so for example:

    a value of 125 could be given,

    =mySheet!$B$10+mySheet!$B$19+mySheet!$B$20

    Would become

    =mySheet!$B$135+mySheet!$B$144+mySheet!$B$145

    I have only used 1 cell as an example here but the reason VBA is needed is because the cells could contain a different formula and I could need to change anything up to 100 or so cells.

    I know its possible as I have done it in the past, but like an idiot i forgot to save it somewhere for future use.

    I hope that makes sense and I haven't overly complicated things.

    The code I have so far is as follows:

    Please Login or Register  to view this content.
    Thanks in advance for any help given.

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: VBA to change cell reference of formula within a given cell

    something like:
    Please Login or Register  to view this content.
    Might work but it depends how much your formulas vary from the example.

  3. #3
    Registered User
    Join Date
    01-07-2014
    Location
    Bath, England
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: VBA to change cell reference of formula within a given cell

    Quote Originally Posted by yudlugar View Post
    something like:
    Please Login or Register  to view this content.
    Might work but it depends how much your formulas vary from the example.
    You must headed in the right direction, but instead of adding the number it just appends it, so:

    =mySheet!$B$10+mySheet!$B$19+mySheet!$B$20

    becomes

    =mySheet!$B$10125+mySheet!$B$19125+mySheet!$B$20125

  4. #4
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: VBA to change cell reference of formula within a given cell

    hi - it worked ok on the example I tried it with so not sure what is happening for you. Perhaps try:
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    01-07-2014
    Location
    Bath, England
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: VBA to change cell reference of formula within a given cell

    I now get type mismatch on this line:
    Please Login or Register  to view this content.

  6. #6
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: VBA to change cell reference of formula within a given cell

    Sorry, both solutions worked ok on my machine. I don't know what the problem is.

    Are you using the code exactly as posted?

  7. #7
    Registered User
    Join Date
    01-07-2014
    Location
    Bath, England
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: VBA to change cell reference of formula within a given cell

    I had to slightly change it as the value to add would change each time so this is how I reused it, perhaps I butchered it somehow?
    Please Login or Register  to view this content.
    I suspect It's got something to do with how 'val' is being set?

  8. #8
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: VBA to change cell reference of formula within a given cell

    Still works for me...

  9. #9
    Registered User
    Join Date
    01-07-2014
    Location
    Bath, England
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: VBA to change cell reference of formula within a given cell

    Hmmm how very odd.
    Possibly version of excel but not sure how this would be an issue in this instance.
    I am using 2007

  10. #10
    Registered User
    Join Date
    01-07-2014
    Location
    Bath, England
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: VBA to change cell reference of formula within a given cell

    Ah ok so if I select few less cells it works, so it clearly doesn't like a particular formula.
    Thanks for your help on this.
    Will give it another bash in the morning.

  11. #11
    Registered User
    Join Date
    01-07-2014
    Location
    Bath, England
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: VBA to change cell reference of formula within a given cell

    Ok I found what was causing the issue, could see in your code that you were using the $ symbol to work out the positions, so looking through the code I could see that some formulas were not absolute.
    Once i change the formulas to absolute via another macro I have, this code worked.

    Many thanks for your help.

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to change a tab name with cell reference and formula?
    By Jared in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-19-2018, 07:10 PM
  2. Dragging formula, change sheet reference, but not cell reference
    By Kalilaya0419 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-13-2013, 04:50 PM
  3. [SOLVED] Change row reference for a cell in formula?
    By gumdrops in forum Excel General
    Replies: 3
    Last Post: 11-20-2012, 05:22 AM
  4. VBA formula - reference particular cell (which can change)
    By ker9 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-27-2010, 03:31 PM
  5. How to change cell color with reference to a formula?
    By Soumadip in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-20-2006, 04:25 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1