VB to find first populated number & then sum range of cells

[LucasLondon]

Hi,

I'm looking for either a VBA or non-VBA solution for this.

Basicly I want it to sum a given number of consecutive rows of values in column J, the first row to include being where the first populated cell appears in column J (as opposed to a formula)

The number of rows to sum would be in the cell u17. So if u17 = 4, then it would sum only the first four consectuive populated cells in column J, the start being where the first populated cell with a number appears.

I've started in trying to determine the start of the range to sum using the code below but this approach does not work because the code finds the first cell with a formula in it and not number.


Sub test()
Sheets("Jamma").Range("J1").End(xlDown).Select
End Sub

Is there any way to find the first populated cell with a number instead? I'm assuming I need to use some worksheet function like isnumber but I'm just guessing at this point. Then I was thinking I could use an offset function to define the range to sum.

Just to clarify, cells in the J column as the following formula in it starting in J4 and running down to J50:

cell J4:
=IF(A4=$S$17,F4*100%,IF(AND(A4>$S$17,ROW()<=50),J3*(1-$T$17),""))

Hope someone can help.

Thanks,

Lucas

[mdmackillop]

Here's a UDF (User Defined Function).
Insert "=CountJ()" in any cell.


Function CountJ()
Dim r As Range
For Each r In Range("J:J")
If Not (r.Formula Like "=*" Or r.Formula = "") Then
Exit For
End If
Next
CountJ = Application.Sum(Range(r, r.Offset(Range("U17") - 1)))
End Function


If you need the result to be dynamic, you'll have to insert the Range into the brackets "=CountJ(J:J)"



Function CountJ(Data As Range)
Dim r As Range
For Each r In Data
If Not (r.Formula Like "=*" Or r.Formula = "") Then
Exit For
End If
Next
CountJ = Application.Sum(Range(r, r.Offset(Range("U17") - 1)))
End Function

[LucasLondon]

Hello

Mdmackillop, Thanks for the code. I tried running both versions but the UDF returns the following in the cell:
#VALUE!


I'm thinking this might be because I may have not explained myself crystal clear and the code you provided is seaching for purely a number as opposed to a formula that returns a number.

To clarfiy, column J (cells J1 to J50) as a formula in it. This formula returns either a value (as in number) or "", i.e nothing. Whether a formula returns a value depends on another argument used in the formula: IF(A2=$S$17,F2*100%).

So whenever the value in S17 changes, the cells populated change. But the cells are populated in a continous block. So currently S17 = Apr 05 and this is what appears in the sheet:

J1 Indentifer
j2 Blank
j3 Formula returning blank
j4 Formula returning blank
j5 Formula returning value/number
j6 Formula returning value/number
j7 Formula returning value/number
j8 Formula returning value/number
j9 Formula returning value/number
Ditto for rest of the cells to j51

I only want to sum the cells where the formula is returning a number, not where it returns blanks. So in the above case, if cell U17 = 2 then I want to only sum J5 to J7, if u17 = 4 then sum J5 to J9.

But it's a dynamic formula so when the argument in the formula s17 changes, so does the the start of cells in which values are returned instead of blanks by the formula. A different value in cell s17 would could for instance the formula to start returning values from J8 onwards instead of j5 as above.

In terms of what I need, if it's easier to do, an alternative approch might be better. Instead of returning the sum of the required range, to actually return the values that would be included in that sum in another adjacent column.

If I can get the range to sum to appear in another column, e.g column K, then I can just place an ordinary sum function accross the range to get the orginal calculation I wanted.

Lucas

[mdmackillop]

Can you post a sample?

[LucasLondon]

Hi,

I've attached an example. In this sheet whenever the value of T17 changes, so does where the data begins to populate in column D. In cell V17 is the number of months I want to sum which again is dynamic based on user input.

If the value in this cell is 8 then in column K I want to show the first 8 consequtive numbers from column D, i.e as it stands currently, k22:K29 would be populated with the equivalent in those rows from column D. If V17 changes to 4 then I only want to show the first four contionous numbers from column D in column K.

Thanks,

Lucas

[Bob Phillips]

Try this in K2


=IF(D2="","",IF(SUMPRODUCT(--(LEN($K$1:K1)>0))<$V$17,D2,""))

and copy down

[LucasLondon]

Xld,

Thanks for this, I've tested it out and works great. I'm going to look up the sumproduct function in order to help me work out how it works. Just one the thing though, the following bit in the formula: --

What exactly does that bit do? I'm confused, I've never seen anything like that before!

Thanks,

Lucas

[Bob Phillips]

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation.