Sumeif macro with range selection

[orquidea]

Hi:

I am trying to do a macro wich works with sumif in a range selected and
after that look for the next active cell, select the range and work again
with the sumif formula.
This is what I have so far:

Range("A2").Select
Selection.End(xlDown).Select

FirstRow= I don't know how to define this one,
LastRow=Cells(Rows.Count,"A").End(xlup).Row
Set criteriarange=Range( I don' know & LastRow)
Setsumrange(Range(I don't know &LastRow)
Newsum=WorksheetFunction.Sumif(Criteriarange,"=20",sumrange)

The ranges will be like below
6 40
1 40
4 40


2 20
1 40
84 40


6 40
3 40
5 40

Could anyone help me please? It would be greatly appreciated.

Thanks
Orquidea

 

[excelent]

Sub SumIf20()
x = Cells(65500, "A").End(xlUp).Row
y = Application.SumIf(Range("B1:B" & x), "=20", Range("A1:A" & x))
MsgBox y
End Sub


"orquidea" skrev:

 

[Bob Phillips]

Dim NextRow As Long
Dim EndRow As Long
Dim LastRow As Long
Dim SumRange As Range
Dim CriteriaRange As Range
Dim NewSum As Double

NextRow = 2
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Do While Cells(NextRow, "A").Value <> ""
EndRow = Cells(NextRow, "A").End(xlDown).Row
Set SumRange = Range(Cells(NextRow, "A"), Cells(EndRow, "A"))
Set CriteriaRange = Range(Cells(NextRow, "B"), Cells(EndRow, "B"))
NewSum = Application.SumIf(CriteriaRange, "=20", SumRange)
MsgBox "Sum starting in row " & NextRow & " is " & NewSum

NextRow = EndRow + 1
If NextRow < LastRow Then
Do While Cells(NextRow, "A").Value = ""
NextRow = NextRow + 1
Loop
End If
Loop

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[orquidea]

Hi Bob

Thanks a lot for your answer. It gives me the result in a MsgBox, but what
I need to asign each result of each range of selection to a different
variable, because I will use these results for other calculations. For
instance, the result of this calculation will be displayed using the below
procesure.

Range("H1").Select
Selection.Value = "Atlantic " & Atln20 & " - 20's"

Could you please help me to achive what I need. You are being so helpful.

Orquidea.

 

[orquidea]

Bob

Just adding to my other message, if you help me to define what you call
"NextRow" under the new scenario I have explained, I think I can take it from
there. I am a rookie in macros.

Thanks,

 

[orquidea]

Thanks.

Sub SumIf20()
x = Cells(65500, "A").End(xlUp).Row
y = Application.SumIf(Range("B1:B" & x), "=20", Range("A1:A" & x))
MsgBox y
End Sub


"orquidea" skrev:

 

[orquidea]

Hi Bob

This is me again. I figured out how to do it, based on your macro.

Thanks a lot

Orquidea

 

[Bob Phillips]

Do you want to post it for posterity?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[orquidea]

Sub Calculations()
Range("A1").Select

FirstRow = ActiveCell.Row
EndRow = Cells(FirstRow, "A").End(xlDown).Row

Set SumRange = Range(Cells(FirstRow, "A"), Cells(EndRow, "A"))
Set CriteriaRange = Range(Cells(FirstRow, "B"), Cells(EndRow, "B"))
Atln20 = Application.SumIf(CriteriaRange, "=20", SumRange)
Atln40 = Application.SumIf(CriteriaRange, "=40", SumRange)


Range("k1").Select
Selection.Value = "Atlantic " & Atln20 & " - 20's" & Atln40 & " -
40's"

 

[Bob Phillips]

Thanks for that.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[supoch14]

Hi:

I am trying to do a macro wich works with sumif in a range selected and
after that look for the next active cell, select the range and work again
with the sumif formula.
This is what I have so far:

Range("A2").Select
Selection.End(xlDown).Select

FirstRow= I don't know how to define this one,
LastRow=Cells(Rows.Count,"A").End(xlup).Row
Set criteriarange=Range( I don' know & LastRow)
Setsumrange(Range(I don't know &LastRow)
Newsum=WorksheetFunction.Sumif(Criteriarange,"=20",sumrange)

The ranges will be like below
6 40
1 40
4 40


2 20
1 40
84 40


6 40
3 40
5 40

Could anyone help me please? It would be greatly appreciated.

Thanks
Orquidea

 

[supoch14]

Hi:

I am trying to do a macro wich works with sumif in a range selected and
after that look for the next active cell, select the range and work again
with the sumif formula.
This is what I have so far:

Range("A2").Select
Selection.End(xlDown).Select

FirstRow= I don't know how to define this one,
LastRow=Cells(Rows.Count,"A").End(xlup).Row
Set criteriarange=Range( I don' know & LastRow)
Setsumrange(Range(I don't know &LastRow)
Newsum=WorksheetFunction.Sumif(Criteriarange,"=20",sumrange)

The ranges will be like below
6 40
1 40
4 40


2 20
1 40
84 40


6 40
3 40
5 40

Could anyone help me please? It would be greatly appreciated.

Thanks
Orquidea

 

[supoch14]

Thanks.

 

[supoch14]

Sub SumIf20()
x = Cells(65500, "A").End(xlUp).Row
y = Application.SumIf(Range("B1:B" & x), "=20", Range("A1:A" & x))
MsgBox y
End Sub


"orquidea" skrev:

 

[supoch14]

Dim NextRow As Long
Dim EndRow As Long
Dim LastRow As Long
Dim SumRange As Range
Dim CriteriaRange As Range
Dim NewSum As Double

NextRow = 2
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Do While Cells(NextRow, "A").Value <> ""
EndRow = Cells(NextRow, "A").End(xlDown).Row
Set SumRange = Range(Cells(NextRow, "A"), Cells(EndRow, "A"))
Set CriteriaRange = Range(Cells(NextRow, "B"), Cells(EndRow, "B"))
NewSum = Application.SumIf(CriteriaRange, "=20", SumRange)
MsgBox "Sum starting in row " & NextRow & " is " & NewSum

NextRow = EndRow + 1
If NextRow < LastRow Then
Do While Cells(NextRow, "A").Value = ""
NextRow = NextRow + 1
Loop
End If
Loop

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)