+ Reply to Thread
Results 1 to 16 of 16

Return relative row with R1C1 reference style

  1. #1
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Return relative row with R1C1 reference style

    Hi all,

    I need to create a dynamic file path with VBA, where the correct name of the folder depends on each cell in a range.

    Please Login or Register  to view this content.
    e.g.

    Cell (B25) = Spring2019
    Cell (B26) = Spring2018
    Cell (B27) = Spring2019
    Cell (B28) = Spring2017
    Cell (B29) = Spring2018

    Pasted formula in cells:

    Cell (C25) = IF(B25="","",INDEX('S:\COMMON-HK2\Cluster Plan - Mens - 2019 - SPWR.xlsb'!Attribute_Material,A25))
    Cell (C26) = IF(B26="","",INDEX('S:\COMMON-HK2\Cluster Plan - Mens - 2018 - SPWR.xlsb'!Attribute_Material,A26))
    Cell (C27) = IF(B27="","",INDEX('S:\COMMON-HK2\Cluster Plan - Mens - 2019 - SPWR.xlsb'!Attribute_Material,A27))
    Cell (C28) = IF(B28="","",INDEX('S:\COMMON-HK2\Cluster Plan - Mens - 2017 - SPWR.xlsb'!Attribute_Material,A28))
    Cell (C29) = IF(B29="","",INDEX('S:\COMMON-HK2\Cluster Plan - Mens - 2018 - SPWR.xlsb'!Attribute_Material,A29))

    However, below does not seem to work. I was hoping I can combine the R1C1 referencing with a formula to get the last 4 characters from the relative cell in column B.

    Please Login or Register  to view this content.

  2. #2
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,496

    Re: Return relative row with R1C1 reference style

    Put your formula with A1 type cell references in a cell and run this code. You'll see the proper R1C1 style formula.

    Please Login or Register  to view this content.
    Enter the formula in a cell.
    Select the cell.
    Tools, Macro, Record
    or
    Developer, Record Macro, OK
    F2
    Enter

    Now look for the macro you just recorded.
    It will have the formula for VBA.
    Last edited by jolivanes; 09-06-2018 at 01:02 AM. Reason: code tags

  3. #3
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: Return relative row with R1C1 reference style

    Since I am trying to create a dynamic file path within a formula, I cannot use the method you described...

  4. #4
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Return relative row with R1C1 reference style

    You’ll have to loop through the cells.
    Rory

  5. #5
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: Return relative row with R1C1 reference style

    Makes sense... how would such a solution look like?

  6. #6
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Return relative row with R1C1 reference style

    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: Return relative row with R1C1 reference style

    Thanks, that works.

    However, it runs so slow that it is almost unusable. Are these "loop through cells" codes always this inefficient? Previous code took only 1 second, now it runs for minutes (!)...

  8. #8
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Return relative row with R1C1 reference style

    Did you turn off Screenupdating and Calculation ?
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  9. #9
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: Return relative row with R1C1 reference style

    Quote Originally Posted by bakerman2 View Post
    Did you turn off Screenupdating and Calculation ?
    I did!!

  10. #10
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Return relative row with R1C1 reference style

    Post the full code, please.

    It's not surprising that this will be slower than the original since the original didn't work.

  11. #11
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: Return relative row with R1C1 reference style

    Here you go:

    Please Login or Register  to view this content.

  12. #12
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Return relative row with R1C1 reference style

    There is no loop in there. You should only turn screenupdating back on after turning calculation back to automatic.

  13. #13
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: Return relative row with R1C1 reference style

    Just tried below code with your suggested loop and ScreenUpdating turned back on last. I had to stop the code after 20 minutes as it was still running...

    Please Login or Register  to view this content.

  14. #14
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Return relative row with R1C1 reference style

    How many different files are you linking to? Can you open them first?

  15. #15
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: Return relative row with R1C1 reference style

    Only 3 in total! Opening them first would be time consuming also as they are relatively large...

  16. #16
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Return relative row with R1C1 reference style

    That may be why it is slow.

+ 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] VBA coding with R1C1 reference style
    By Alan Beban in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-01-2018, 08:51 PM
  2. absolute cell reference in R1C1 reference style
    By Prorocentrum in forum Excel General
    Replies: 1
    Last Post: 06-18-2007, 04:59 PM
  3. Stuck in R1C1 reference style
    By 2504 in forum Excel General
    Replies: 9
    Last Post: 12-30-2006, 09:05 PM
  4. R1C1 style reference as Condition
    By aca in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-07-2006, 08:40 AM
  5. How to code with the R1C1 Style of Reference?
    By plh in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-22-2006, 02:35 PM
  6. R1C1 Reference Style won't STAY gone?
    By khrystle in forum Excel General
    Replies: 5
    Last Post: 03-01-2006, 06:40 PM
  7. [SOLVED] R1C1 reference style
    By Peg P in forum Excel General
    Replies: 2
    Last Post: 11-15-2005, 02:50 PM

Tags for this Thread

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