+ Reply to Thread
Results 1 to 15 of 15

Change the cell reference in formula to absolute for part of cell via sub routin

  1. #1
    Forum Contributor
    Join Date
    07-10-2019
    Location
    england
    MS-Off Ver
    2013
    Posts
    486

    Change the cell reference in formula to absolute for part of cell via sub routin

    Hi All,
    +SUMIF('account_type_transactions (2)'!T:T,account_transactions!T2,'account_type_transactions (2)'!O:O)

    Here i want to change the account_type_transaction T:T to absolute in the whole file where ever it appears. I have 3 sheet tabs containing this formula for 100 rows for each sheet.
    I need a function or sub routine.
    Last edited by dorabajji; 10-07-2019 at 03:59 PM.

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: I want to change the cell reference in formula to absolute for part through a function

    You can simply use Search & Replace (CTRL + H).

    Set to look in Formula and within Workbook context.

    Then search for !T:T and replace with !$T:$T
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Forum Contributor
    Join Date
    07-10-2019
    Location
    england
    MS-Off Ver
    2013
    Posts
    486

    Re: I want to change the cell reference in formula to absolute for part through a function

    Hi Sir,
    No,
    I have formulas for this account_type_transactions.. other than sumif, sumproduct to i have..
    So where ever this sheet is used for formula, i want to change those range alone to absolute. Would be easy if a funtion or subroutine is there..

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: I want to change the cell reference in formula to absolute for part through a function

    So just search for SUMIF('account_type_transactions (2)'!T:T and replace with SUMIF('account_type_transactions (2)'!$T:$T

    If you need VBA, just record the process

  5. #5
    Forum Contributor
    Join Date
    07-10-2019
    Location
    england
    MS-Off Ver
    2013
    Posts
    486

    Re: I want to change the cell reference in formula to absolute for part through a function

    i tried.. but am not getting it correct.. I checked net and got one sub routine.. but it changes full cell..
    Please Login or Register  to view this content.
    But want to know, how to tweak to one part of cell?

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: I want to change the cell reference in formula to absolute for part of cell via sub ro

    Why .FormulaR1C1?
    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    07-10-2019
    Location
    england
    MS-Off Ver
    2013
    Posts
    486

    Re: Change the cell reference in formula to absolute for part of cell via sub routin

    Hi,
    The problem is , as i said.. i also want to change the same sheet reference for eg: SUMIF('account_type_transactions (2)'!r:r also..

    I have same sheet reference in three formulas and also different columns also as in the above eg:
    I want all at once.. 6 files like this..

    Please Login or Register  to view this content.
    I am trying to modify the function to find instead of left and right function..
    is it possible?
    so that i can apply the convert formula in the required part of the cell alone..

  8. #8
    Forum Contributor
    Join Date
    07-10-2019
    Location
    england
    MS-Off Ver
    2013
    Posts
    486

    Re: Change the cell reference in formula to absolute for part of cell via sub routin

    first i am converting everything in the formula to absolute using the
    Please Login or Register  to view this content.
    Then i want to replace $ for not representing account_type_transaction sheet.. using the above kind of function either using find replace or left/right and instr
    is it possible and am i going correct?

  9. #9
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Change the cell reference in formula to absolute for part of cell via sub routin

    Not using FormulaR1C1 or Formula.

    You'll just have to loop using Replace logic. Either via Array() and list of letter/column reference, or using CHR().

  10. #10
    Forum Contributor
    Join Date
    07-10-2019
    Location
    england
    MS-Off Ver
    2013
    Posts
    486
    Quote Originally Posted by CK76 View Post
    Not using FormulaR1C1 or Formula.

    You'll just have to loop using Replace logic. Either via Array() and list of letter/column reference, or using CHR().
    I used instr.. I am not well versed in array..
    I tried array, but how to convert back the array to range,I didn't know..
    I used selection and completed 2 files.. with that..
    It took me 30 of macro runtime..
    I think it is not efficient,but worked..
    I just have one doubt.
    If array is used, then in the convert formula I am providing the range loop of each cell in rangevariable onecell,can I convert that into array?
    How to put the convert formula application into array and transfer back to range?

  11. #11
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Change the cell reference in formula to absolute for part of cell via sub routin

    No, I mean hold array of texts that you need to replace.

    Ex:
    Please Login or Register  to view this content.
    But to be honest, I see no point in doing this through code. It's not like you'll need to replace reference more than once...

  12. #12
    Forum Contributor
    Join Date
    07-10-2019
    Location
    england
    MS-Off Ver
    2013
    Posts
    486

    Re: Change the cell reference in formula to absolute for part of cell via sub routin

    Yes sir,
    but this is useful for changing reference..
    One more doubt i have..
    should i do a new post for that, as it is related to formula throwing error in changing relative to abs

  13. #13
    Forum Contributor
    Join Date
    07-10-2019
    Location
    england
    MS-Off Ver
    2013
    Posts
    486

    Re: Change the cell reference in formula to absolute for part of cell via sub routin

    Hi CK76 sir ,
    If for any range in worksheet, i want to change it to abs reference. If the limit of char is more than 255, is there any custom function?
    or can there is a turnaround?

  14. #14
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Change the cell reference in formula to absolute for part of cell via sub routin

    I'd recommend that you start new thread with sample workbook that clearly demonstrates your need.

    That is quite a bit different issue than what's indicated in your initial question.

  15. #15
    Forum Contributor
    Join Date
    07-10-2019
    Location
    england
    MS-Off Ver
    2013
    Posts
    486

    Re: Change the cell reference in formula to absolute for part of cell via sub routin

    Yes, i had started another thread sir and posted the code too.. error in the code heading..
    if possible , do check that.. if you can..

+ 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. [SOLVED] Absolute reference for part of formula
    By aaaaaaiden in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-14-2014, 09:05 AM
  2. [SOLVED] Modify change event to absolute cell reference?
    By Marvelous in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-10-2013, 07:03 PM
  3. [SOLVED] Replace absolute cell reference with Indirect cell reference in formula
    By Roothy in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-11-2013, 04:46 AM
  4. Change a macro to from and absolute cell reference to selected cell.
    By dcgrove in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-05-2010, 01:19 PM
  5. How to change absolute cell reference with formula
    By mzypt in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-10-2008, 11:36 AM
  6. [SOLVED] Change linked cell reference to absolute
    By RAP in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-22-2005, 11:05 AM
  7. [SOLVED] How do I change a cell from absolute reference to relative referen
    By simonsez in forum Excel General
    Replies: 1
    Last Post: 05-17-2005, 04:06 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