ExcelBanter - Sumeif macro with range selection

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Sumeif macro with range selection (https://www.excelbanter.com/excel-discussion-misc-queries/169027-sumeif-macro-range-selection.html)

orquidea

Sumeif macro with range selection

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

Sumeif macro with range selection

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:

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

Bob Phillips

Sumeif macro with range selection

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" wrote in message
...
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

orquidea

Sumeif macro with range selection

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.

"Bob Phillips" wrote:

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" wrote in message
...
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

orquidea

Sumeif macro with range selection

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" wrote:

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.

"Bob Phillips" wrote:

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" wrote in message
...
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

orquidea

Sumeif macro with range selection

Thanks.

"excelent" wrote:

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:

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

orquidea

Sumeif macro with range selection

Hi Bob

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

Thanks a lot

Orquidea

"Bob Phillips" wrote:

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" wrote in message
...
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

Bob Phillips

Sumeif macro with range selection

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" wrote in message
...
Hi Bob

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

Thanks a lot

Orquidea

"Bob Phillips" wrote:

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" wrote in message
...
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

orquidea

Sumeif macro with range selection

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" wrote:

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" wrote in message
...
Hi Bob

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

Thanks a lot

Orquidea

"Bob Phillips" wrote:

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" wrote in message
...
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

Bob Phillips

Sumeif macro with range selection

Thanks for that.

--
---
HTH

Bob

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

"orquidea" wrote in message
...
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" wrote:

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" wrote in message
...
Hi Bob

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

Thanks a lot

Orquidea

"Bob Phillips" wrote:

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" wrote in message
...
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

Sumeif macro with range selection

"orquidea" wrote:

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

Sumeif macro with range selection

supoch14

Sumeif macro with range selection

"orquidea" wrote:

Thanks.

"excelent" wrote:

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:

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

Sumeif macro with range selection

"excelent" wrote:

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:

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

Sumeif macro with range selection

"Bob Phillips" wrote:

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" wrote in message
...
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

All times are GMT +1. The time now is 06:28 AM.