MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Multiple selection criteria using named ranges - can't get it


Posted by Darren S on August 17, 2001 2:02 AM

In VBA I am trying to name a range of cells (AllTransactionDates & AllTransactionAmounts) that changes length on a daily basis. I intend to use this named range as part of a multiple criteria sum.

{=SUM((AllTransactionDates>1/1/2001)*(AllTransactionDates<=A3)*AllTransactionAmounts)}

When the range is named manually calculation works correctly.

As the range is added to on a daily basis my plan was to delete the named range in VBA and using

Sheets("All Transactions").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select

rename the range. My attempts have not worked. I am able to get the range named however the calculation does not seem to work. I suspect it has something to do with the name range wanting to using R1C1 type address and the selection method returns a cell address.

I found the following code that I thought might work, however I seem to have disabled the use of a reserved word "Address" in my project somehow. I was planning on returning the result of the following line to a variable to assist in the naming.

Address(2, 1, 1, False, "All Transactions")

however when I type Address the computer changes it to address, I at one stage used this word as a variable but it no longer exists now.

Is there an answer. The easiest way as I see it is if a named range can be created in VBA based upon the last cell used in a row.

Assistance is appreciated. I am going nuts.


Posted by Aladin Akyurek on August 17, 2001 2:38 AM

Darren,

I don't see why you'd need VBA to create a named dynamic range. I propose 2 ways that you could use:

(1) I reckon AllTransactionDates and AllTranactionAmounts are the names that you want to assign to continually/regularly changing ranges.

Activate the first cell of the range for AllTransactionDates. Lets say this cell is A2 on sheet X.
Activate Insert|Name|Define.
Enter AllTransctionDates for Names in Workbook.
Enter the following formula for Refers To:

=OFFSET(x!$A$2,0,0,COUNTA($A:$A),1)

Follow the above proc for the range that will get the name AllTransactionAmounts.

Your array formula will work. Why don't you place your date condition in say A2 and use

{=SUM((AllTransactionDates>A1)*(AllTransactionDates<=A3)*AllTransactionAmounts)}

By the way, you might want to use the non-array formula instead:

=SUMPRODUCT((AllTransactionDates>1/1/2001)*(AllTransactionDates<=A3)*AllTransactionAmounts)}

(2) If the formula is, this is of utmost importance, on the same sheet as the ranges it process, you can use a UDF (see below) instead of named dynamic ranges with the same functionality (keeping track of changes in the relevant ranges). If so or you can meet the precondition just mentioned, add the following UDF to your workbook (via Insert|Module):

Function Used(r As Range) As Range
'
' Harlan Grove
'
Dim q As Range
Set q = r.Parent.UsedRange.Cells(r.Parent.UsedRange.Cells.Count)
Set Used = Intersect(r, r.Parent.Range(Cells(1, 1), q))
End Function

Now, you can re-write the formula, where I assume, in order to illustrate the usage, dates in column C and amounts in D on sheet X.

The SUMPRODUCT (or the array formula) must be re-written as follows and MUST BE ENTERED somewhere in shhet X:

=SUMPRODUCT((USED(C:C)>DateCrit1)*(USED(C:C)<=DateCrit2)*(USED(D:D))

where DateCrit1 and DateCrit2 are lower and upper date points that you want to use as criteria. These criterion dates can be placed anywhere in your workbook.

Aladin

Posted by Darren S on August 17, 2001 3:21 PM

Unexpected result - Value too high.

Aladin I have used your method as recommended to create the named range using the formula supplied

=OFFSET('All Transactions'!$A$2,0,0,COUNTA('All Transactions'!$A:$A),1)

This is what I have in for AllTransactionDates, I found that not having the ' symbol around the sheet name resulted in #Name when using the formaula, again I used the sum product formula you recommended

=SUMPRODUCT((AllTransactionDates>1/1/2001)*(AllTransactionDates<=A3)*AllTransactionAmounts)

I am currently only using the formuala on one cell when I transfer to other cells I will remove the fixed date and use a cell reference. One doesn't exist for starting range at the moment. Subsequent cells will.

Using the above information results in a value far in excess of what it should be. This also occurs when the array formuala is used. It must be something to do with the named range?

Results 556160
Expected 2749.68

Have I done something wrong?

Thanks

Darren.

Posted by Aladin Akyurek on August 17, 2001 3:34 PM

Re: Unexpected result - Value too high.

That is to be expected with a space in the sheet name. If you have a space, you have to put the name between single quotes. I prefer not to use spaces in sheet names, even in column headings.

when using the formaula, again I used the sum product formula you recommended

It would suprise me if they didn't produce the same result.

It must be something to do with the named range?

No, I don't believe so.

Hard to say. And I'm clueless. If you want me to look at your model, you have my e-mail address.

Aladin

Posted by DarrenS on August 17, 2001 3:54 PM

Re: Unexpected result - Value too high.

Aladin,

Thanks for help, I found that when I pasted the formula you suggested into my named range for TransactionAmounts I did not change the starting cell from A2 and column A:A, after changing it, everything worked.

Posted by Aladin Akyurek on August 17, 2001 4:03 PM

That is great. (NT)