+ Reply to Thread
Results 1 to 9 of 9

Macro: mixed absolute and relative references for rows

  1. #1
    Registered User
    Join Date
    07-31-2012
    Location
    WI
    MS-Off Ver
    Excel 2010
    Posts
    5

    Macro: mixed absolute and relative references for rows

    Hi there,
    I have a problem with the macro I'm trying to set up. I have the equation:

    =MIN(IF($B$2:$B$5000=MAX(IF($B$2:$B$5000<(INT($B2)+1),$B$2:$B$5000,"")),$G$2:$G$5000,""))

    that I want to convert into a macro. I need everything to be an absolute reference except for the $B2 of the INT() function. I want that to change based on the row that the data is being entered into (so row 2 is $B2, row 3 reads $B3, etc). However, here's my problem (and which is why I can't figure out a solution to adapt in other posts), the equation above is located in each cell in column H, but the data being entered is in column G. For example

    A B C D E F G H
    1
    1.2
    1.3 8/6/12
    2

    so using the equation (desired macro) above, H1, H2, and H3 would all populate with 8/6/12.

    As a side note, even though I put "" in my equation, it still returns 0 (in this case, 1/0/1900) if the G cell is blank. Anyone know how to fix that?

    Thanks for your help!

    ---------- Post added at 10:01 PM ---------- Previous post was at 09:58 PM ----------

    Oops! Sorry. My example didn't translate very well. The 1, 1.2, 1.3, and 2 are in B1, B2, B3, and B4, respectively, and the 8/6/12 is in cell G3


    Thanks!
    Last edited by Jendill; 08-12-2012 at 08:16 PM.

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Macro: mixed absolute and relative references for rows

    Hi

    Do you want the formula to be evaluated in the macro, and the output posted to the relevant cell, or do you want the macro to write the formula to a cell, then copy it down?

    rylo

  3. #3
    Registered User
    Join Date
    07-31-2012
    Location
    WI
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Macro: mixed absolute and relative references for rows

    Hi rylo,
    Ideally, I would like the formula to be evaluated in the macro and the output posted to the relevant cell(s). If I copy and paste this equation into each cell (without using a macro), it works, but it slows the spreadsheet calculations significantly. Thats why I wanted to figure out how to get it to work with a macro.

  4. #4
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Macro: mixed absolute and relative references for rows

    Hi

    What cell contains the formula you have provided in #1? Is is H1 or H2?

    If it is in H2, and your formulas should start in H1, then try

    Please Login or Register  to view this content.

    rylo
    Last edited by rylo; 08-12-2012 at 06:08 PM.

  5. #5
    Registered User
    Join Date
    07-31-2012
    Location
    WI
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Macro: mixed absolute and relative references for rows

    Hi rylo,
    Thanks for the macro and all of your help. It didn't really work for me -- it put my spreadsheet in some sort of loop and then crashed. It could be due to the other macros/formatting/filtering I have in place. I think I'm just going to include the equation in the actual spreadsheet, but not as far down (its a "to be filled in" spreadsheet rather than one thats already prepared.).

    Thanks

  6. #6
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Macro: mixed absolute and relative references for rows

    Hi

    OK. Rather than having it go to a fixed range, how about you have a couple of dynamic defined names that will automatically increase whenever say a new item is put into column B? That way you don't have to worry about the size of the ranges as they would automatically update.

    rylo

  7. #7
    Registered User
    Join Date
    07-31-2012
    Location
    WI
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Macro: mixed absolute and relative references for rows

    Hi rylo,
    That would work. How would I do that?

    Thanks!

  8. #8
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Macro: mixed absolute and relative references for rows

    Hi

    Not sure with 2010, but try something like
    Formulas, name Manager, new
    Name: colb, Refers To: =OFFSET(Sheet1!$B$1,0,0,COUNT(Sheet1!$B:$B),1)
    Name: colg, refers to: =OFFSET(colb,0,5)

    Then you replace the references to the B and G ranges with the above names. What will happen is that as you add a new number to column B, the named range colb will increase. colg is offset from colb, so as it increases, so will colg.

    See how you go.

    rylo

  9. #9
    Registered User
    Join Date
    07-31-2012
    Location
    WI
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Macro: mixed absolute and relative references for rows

    Hi rylo,
    Fantastic!! This seems to have worked in the "large" range without slowing my spreadsheet. Thats for all your help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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