Sumif

[Fabian]

Hi,

I am loking for a piece of code for the following. I have 100 rows and 39
columns. Every row could be in 3 different ways:
A B C D E F G H
I..............AM
1 0 0 0 100 150 65 85 73
25............89
2 0 0 0 56 130 125 88 178
32...........0
3 98 36 58 89 489 125 88 178
32...........56
I need to sum the first 6 cells with numbers or sales (no with 0's), and
those first 6 numbers for every row could start at any colums of those 39
columns. Example row 1 should sum from D to I, row 2 should sum from D to I,
row 3 from A to F. I am not sure how to start
Thanks

 

[JLGWhiz]

You didn't say where you wanted to put the sum, so I arbitrarily put it in
column 40. You can play with it to suit your purposes.

Sub getSumSixCol()
Dim lr As Long, i As Long, j As Long
lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lr
With ActiveSheet
If .Cells(i, 1) < 1 Then
For j = 1 To 39
If .Cells(i, j) > 0 Then
.Cells(i, 40) = WorksheetFunction.Sum(.Range(.Cells(i, j),
..Cells(i, j + 5)))
Exit For
End If
Next
Else
.Cells(i, 40) = WorksheetFunction.Sum(.Range(.Cells(i, 1), .Cells(i,
6)))
End If
End With
Next
End Sub

 

[Gary Keramidas]

here's one way you can look at for xl2003

Sub test()
Dim ws As Worksheet
Dim lastrow As Long
Dim i As Long
Dim x As Long
Dim cntr As Long
Dim tempval As Double
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To lastrow
x = 1
cntr = 0
tempval = 0
Do While cntr <> 6
With ws
If x = 256 Then Exit Do
If .Cells(i, x).Value > 0 Then
tempval = .Cells(i, x).Value + tempval
Debug.Print .Cells(i, x).Address
cntr = cntr + 1
x = x + 1
Else
x = x + 1
End If
End With
Loop
Debug.Print tempval
Next
End Sub

 

[OssieMac]

Hi Fabian,

Yet another way is to create a UDF (User Defined Function). This allows you
to insert a function in column 40 and sum the first 6 (or whatever number of
cells you like) after the zeros.

Copy the function into a module and in column 40 (assuming you are summing
the values in row 2) insert the following

=SumAftZero2(A2:AM2,6)

A2:AM2 is the full range of 39 cells and 6 is the number of cells to sum
after the zeros. Drag the formula down.

Function SumAftZero(rngRow As Range, numbToSum As Long)
'Produces circular reference error if less cells
'left to sum than parameter numbToSum
Dim rngC As Range
numbToSum = numbToSum - 1

For Each rngC In rngRow
If rngC.Value > 0 Then
Range(rngC, rngC.Offset(0, numbToSum)).Select
SumAftZero = WorksheetFunction.Sum _
(Range(rngC, rngC.Offset(0, numbToSum)))
Exit For
End If
Next rngC

End Function

 

[OssieMac]

Hi again Fabian,

I left a line selecting a range that I was using during test. use the
following instead.

Function SumAftZero(rngRow As Range, numbToSum As Long)
'Produces circular reference error if less cells
'left to sum than parameter numbToSum
Dim rngC As Range
numbToSum = numbToSum - 1

For Each rngC In rngRow
If rngC.Value > 0 Then
SumAftZero = WorksheetFunction.Sum _
(Range(rngC, rngC.Offset(0, numbToSum)))
Exit For
End If
Next rngC

End Function

 

[OssieMac]

Hi yet again Fabian.

I didn't do well at all with this one. The function in column 40 should be

=SumAftZero(A2:AM2,6)

SumAftZero2 was an alternative method I was playing with.

Please accept my apologies for my stuff ups.

 

[Fabian]

Thanks JLGWhiz, this is working great. I will ask you something related in
another post

 

[Fabian]

Thanks Gary, working perfect.. I will ask you some code related to this in
another post

 

[Fabian]

Thanks OssieMac, great idea. I will ask you some code related to this in
another post...