Macro: mixed absolute and relative references for rows

**Jendill** · 08-09-2012, 11:01 PM

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!

**rylo** · 08-09-2012, 11:53 PM

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

**Jendill** · 08-10-2012, 08:10 AM

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.

**rylo** · 08-12-2012, 06:00 PM

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

**Jendill** · 08-12-2012, 06:50 PM

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

**rylo** · 08-12-2012, 07:03 PM

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

**Jendill** · 08-12-2012, 07:06 PM

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

Thanks!

**rylo** · 08-12-2012, 07:34 PM

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

**Jendill** · 08-12-2012, 08:12 PM

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

Macro: mixed absolute and relative references for rows

LinkBack

Thread Tools

Rate This Thread

Display

Macro: mixed absolute and relative references for rows

Re: Macro: mixed absolute and relative references for rows

Re: Macro: mixed absolute and relative references for rows

Re: Macro: mixed absolute and relative references for rows

Re: Macro: mixed absolute and relative references for rows

Re: Macro: mixed absolute and relative references for rows

Re: Macro: mixed absolute and relative references for rows

Re: Macro: mixed absolute and relative references for rows

Re: Macro: mixed absolute and relative references for rows

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions