Dynamic Range for use in Formula

[PhilW_34]

Hello,

Normally, I would spend more time searching for my solution, but today I'm under a time constraint.

I want to enter this formula in cell C3.

=SUMPRODUCT(($B6:$B2199="Amount")*SUBTOTAL(9,OFFSET(C1,ROW($B6:$B2199)-ROW(C1),)))

However, I need $B6:$B2199 to be a dynamic range. Sometimes it could be B6:B200 or B6:B2300, etc. I want to select B6:Last Row in B.

I have the rest of the code pretty much done. I just can't figure out how to define the range and then put it in my formula. Any help would be greatly appreciated.

Thank you.
Phil

 

[3link]

Try creating a named range as follows:

=OFFSET($B$6,0,0,(ROWS($B$6:$B$2199)-COUNTBLANK($B$6:$BN$2199)),1)


This assumes that Row 2199 is the longest it can be. Replace 2199 if needed.

 

[PhilW_34]

Thanks 3link,

I'm actually looking to have this formula entered into the final stages of a macro. I can't see how that formula would solve my problem. I need to determined my range and then enter that range in my formula.

I guess I'm a bit frazzled this morning...lack of sleep and quality coffee. So, my initial request wasn't worded that well.

Thanks,

Phil

 

[3link]

Naw. I just misread. It looks like you just need something to find the last row. This has worked for me:

Lastrow = Columns("B").Find(What:="*", After:=Range("B1"), LookIn:=xlValues, _
        SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

And my range looks something like:

Range("B15:B" & Lastrow).AutoFilter

Ignore the filter stuff. Different macro, but the "last row" principle applies.

 

[T. Valko]

Hello,

Normally, I would spend more time searching for my solution, but today I'm under a time constraint.

I want to enter this formula in cell C3.

=SUMPRODUCT(($B6:$B2199="Amount")*SUBTOTAL(9,OFFSET(C1,ROW($B6:$B2199)-ROW(C1),)))

However, I need $B6:$B2199 to be a dynamic range. Sometimes it could be B6:B200 or B6:B2300, etc. I want to select B6:Last Row in B.

I have the rest of the code pretty much done. I just can't figure out how to define the range and then put it in my formula. Any help would be greatly appreciated.

Thank you.
Phil

Assuming the range contains TEXT only...

=$B$6:INDEX($B$6:$B$65536,MATCH("zzzzz",$B$6:$B$65536))

You can adjust for a reasonable end of range $B$65536.

 

[T. Valko]

Hmmm...

Well, that's what happens when you're a slow typer like me.

Several other replies show up that I didn't see before submitting my own reply.

Oh well!

 

[PhilW_34]

Ok, I see what you are doing. I have code like that which I've gleaned from this site.

Now, how would I put that range in my formula?

Basically, I want to give the range a name so it can go in the formula each time the macro is run.

I envision real code which does something like this...

Dim myRange As Range
myRange = .Range("B5" & Rows.Count).End(xlDown).Row
what I'm missing...

Formula in B3 ends up:
Range("B3").Formula = "=SUMPRODUCT((myRange=""Amount"")*SUBTOTAL(9,OFFSET(C1,ROW(MyRange)-ROW(C1),)))"

Thanks again.

Phil

 

[3link]

Okay I'm a little confused. I think I misread twice now. At first I thought you wanted to create a dynamic range for column B. Then I thought you wanted this in VBA instead of cell C3.

If you're putting this dynmaic range into a cell, my first solution would work.

Create a named range (i.e. "ColB").

ColB = OFFSET($B$6,0,0,(ROWS($B$6:$B$2199)-COUNTBLANK($B$6:$BN$2199)),1)


And then change your formula for C3 to

=SUMPRODUCT((ColB="Amount")*SUBTOTAL(9,OFFSET(C1,ROW($B6:$B2199)-ROW(C1),)))

Can you explain how you want this to be a macro? I don't think it has to be if you just want a variable range.

 

[PhilW_34]

Thanks again 3link.

I help out our international department by formatting some reports they download. After I format the file, I need to put totals for amounts sold at the top of the file. I can enter the formula in the code with a finite range ($B$6:$B$2199). However, this range can vary depending on the report. So what I need is to define or name the range for any one worksheet and then enter that range in the formula for that worksheet only.

So, if they have a worksheet with data down to row 5000, my formula would work there too. I only want to select the number of rows for the worksheet. I don't really want to just add a bunch to the finite range (ie $B$6:$B$6000, etc). So, I need to determined the desired range and then put that as the range in my formula.

Thanks.

Phil

 

[T. Valko]

Thanks again 3link.

I help out our international department by formatting some reports they download. After I format the file, I need to put totals for amounts sold at the top of the file. I can enter the formula in the code with a finite range ($B$6:$B$2199). However, this range can vary depending on the report. So what I need is to define or name the range for any one worksheet and then enter that range in the formula for that worksheet only.

So, if they have a worksheet with data down to row 5000, my formula would work there too. I only want to select the number of rows for the worksheet. I don't really want to just add a bunch to the finite range (ie $B$6:$B$6000, etc). So, I need to determined the desired range and then put that as the range in my formula.

Thanks.

Phil

What I suggested will do what you want, create a dynamic range.

Try it!