Range Formula question
 

I'm new to VBA Excel and need some help creating some code.

I want to sum two columns of data; the number of rows with data is variable.

First sum=: cell F10 through the end of the range (Which I would normally
select by using shift/ctrl/down arrow). The Sum of this range needs to be in
cell G2.

Second Sum: The second sum needs to be the total of the range that starts 3
rows below (i.e. 2 blank rows) through the end of the data in that column
(Again, I use shift/ctrl/down arrow). This sum needs to be in cell G6.

Am having a tough time writing the code correctly to select the two
ranges....thanks for any hints you can provide.

Range Formula question
 

David, experiment in VB with the .End method. Examples of its use a

varLastRow = Range("F10").End(xlDown).Row
varLastAddress = Range("F10").End(xlDown).Address
or try this
Range("G2").Formula = "=SUM(F10:" & Range("F10").End(xlDown).Address

the .End takes one of 4 parameters: xlUp, xlDown, xlToLeft or xlToRight

You'll see this kind of thing used a lot also:
longLastUsedRow = Range("A" & Rows.Count).End(xlUp).Row
which will return the last used cell row number in column A. Assuming that
the last cell in column A (A65536 in Excel 2003) is empty, it would return
the row number of the first cell above it that was not empty.

"David B" wrote:

I'm new to VBA Excel and need some help creating some code.

I want to sum two columns of data; the number of rows with data is variable.

First sum=: cell F10 through the end of the range (Which I would normally
select by using shift/ctrl/down arrow). The Sum of this range needs to be in
cell G2.

Second Sum: The second sum needs to be the total of the range that starts 3
rows below (i.e. 2 blank rows) through the end of the data in that column
(Again, I use shift/ctrl/down arrow). This sum needs to be in cell G6.

Am having a tough time writing the code correctly to select the two
ranges....thanks for any hints you can provide.

Range Formula question
 

Hi David -

If I understand the situation correctly, you have one set of numbers
starting at F10 and then another set of numbers that starts 2 rows below that.

There are MANY ways to solve this problem but here is a really easy way that
I use all the time. Try this:


Sub qwikSum()
Dim ws As Worksheet
Dim r As Range
Dim sum1 As Variant
Dim sum2 As Variant
Dim o As Integer

o = 0
sum1 = 0
sum2 = 0

Set ws = ThisWorkbook.Sheets(yourWorksheetName)
Set r = ws.Range("F10")

While r.Offset(o) < ""
If IsNumeric(r.Offset(o)) Then
sum1 = sum1 + r.Offset(o)
End If
o = o + 1
Wend

' That's sum 1

ws.Range("G2") = sum1

' Sum2 starts 2 rows below this
o = o + 2

While r.Offset(o) < ""
If IsNumeric(r.Offset(o)) Then
sum2 = sum2 + r.Offset(o)
End If
o = o + 1
Wend

' That's sum 2

ws.Range("G6") = sum2
End Sub


I like to call this the "Spider" approach as the sider goes down the column.
It is easy and can be used in a large number of contexts.

Hope that helps,

Chris (ct60)


"David B" wrote:

I'm new to VBA Excel and need some help creating some code.

I want to sum two columns of data; the number of rows with data is variable.

First sum=: cell F10 through the end of the range (Which I would normally
select by using shift/ctrl/down arrow). The Sum of this range needs to be in
cell G2.

Second Sum: The second sum needs to be the total of the range that starts 3
rows below (i.e. 2 blank rows) through the end of the data in that column
(Again, I use shift/ctrl/down arrow). This sum needs to be in cell G6.

Am having a tough time writing the code correctly to select the two
ranges....thanks for any hints you can provide.

Range Formula question
 

Thanks for your reply:

This code:
Range("G2").Formula = "=SUM(F10:" & Range("F10").End(xlDown).Address

looks to be very close to what I'm attempting, but there seems to be either
quotes or parentheses missing that I can't figure out.

"JLatham" wrote:

David, experiment in VB with the .End method. Examples of its use a

varLastRow = Range("F10").End(xlDown).Row
varLastAddress = Range("F10").End(xlDown).Address
or try this
Range("G2").Formula = "=SUM(F10:" & Range("F10").End(xlDown).Address

the .End takes one of 4 parameters: xlUp, xlDown, xlToLeft or xlToRight

You'll see this kind of thing used a lot also:
longLastUsedRow = Range("A" & Rows.Count).End(xlUp).Row
which will return the last used cell row number in column A. Assuming that
the last cell in column A (A65536 in Excel 2003) is empty, it would return
the row number of the first cell above it that was not empty.

"David B" wrote:

I'm new to VBA Excel and need some help creating some code.

I want to sum two columns of data; the number of rows with data is variable.

First sum=: cell F10 through the end of the range (Which I would normally
select by using shift/ctrl/down arrow). The Sum of this range needs to be in
cell G2.

Second Sum: The second sum needs to be the total of the range that starts 3
rows below (i.e. 2 blank rows) through the end of the data in that column
(Again, I use shift/ctrl/down arrow). This sum needs to be in cell G6.

Am having a tough time writing the code correctly to select the two
ranges....thanks for any hints you can provide.

Range Formula question
 

Ok, you need to come up with another range that starts 3 rows down from the
end of the other. You already have the address of the end of the other, or
at least you know how to get it.

We'll play with strings here, making up variable names along the way <g

nextStartAddress = Range("F10").End(xlDown).Offset(3, 0).Address
nextEndAddress = Range(nextStartAddress).end(xldown).Address
now you have the two addresses you need
(let's see if I can build formula properly this time!)
Range("G6").Formula = "=SUM(" & nextStartAddress & ":" & nextEndAddress & ")"

You can play this chase the tail thing all the way down the column. Hard
part might be in deciding when you run out of groups. You can set something
like veryLastRow = Range("F" & Rows.Count).End(xlUP).Row
and test any new point found's .Row property to see if it is greater than
veryLastRow and it if is, you know you are out of data to work with.

"David B" wrote:

Excellent!

now, I need to select the next batch of data in Column "F" (which starts 3
rows below the end of the data in the previous formula) and put the sum in
cell G6.

I think I can make this work...but all input is welcomed!

"Dave Peterson" wrote:

Range("G2").Formula = "=SUM(F10:" & Range("F10").End(xlDown).Address & ")"



David B wrote:

Thanks for your reply:

This code:
Range("G2").Formula = "=SUM(F10:" & Range("F10").End(xlDown).Address

looks to be very close to what I'm attempting, but there seems to be either
quotes or parentheses missing that I can't figure out.

"JLatham" wrote:

David, experiment in VB with the .End method. Examples of its use a

varLastRow = Range("F10").End(xlDown).Row
varLastAddress = Range("F10").End(xlDown).Address
or try this
Range("G2").Formula = "=SUM(F10:" & Range("F10").End(xlDown).Address

the .End takes one of 4 parameters: xlUp, xlDown, xlToLeft or xlToRight

You'll see this kind of thing used a lot also:
longLastUsedRow = Range("A" & Rows.Count).End(xlUp).Row
which will return the last used cell row number in column A. Assuming that
the last cell in column A (A65536 in Excel 2003) is empty, it would return
the row number of the first cell above it that was not empty.

"David B" wrote:

I'm new to VBA Excel and need some help creating some code.

I want to sum two columns of data; the number of rows with data is variable.

First sum=: cell F10 through the end of the range (Which I would normally
select by using shift/ctrl/down arrow). The Sum of this range needs to be in
cell G2.

Second Sum: The second sum needs to be the total of the range that starts 3
rows below (i.e. 2 blank rows) through the end of the data in that column
(Again, I use shift/ctrl/down arrow). This sum needs to be in cell G6.

Am having a tough time writing the code correctly to select the two
ranges....thanks for any hints you can provide.

--

Dave Peterson

Range Formula question
 

JLatham:

Thank you for your help...this stuff is starting to make some sense to me.

I understand what the code you've written is intending to do, and that's
exactly what I'm wanting to accomplish, but I think I'm missing some basic
elements as I drop it into my worksheet code.

Specifically, I'm getting an error message of "variable not defined" for the
'nextStartAddress'...I presume this would be the same for 'nextEndAddress'. I
had thougtht I needed a "Dim" statement (i.e. "Dim nextStartAddress As
Range") but that returns an error as well.

"JLatham" wrote:

Ok, you need to come up with another range that starts 3 rows down from the
end of the other. You already have the address of the end of the other, or
at least you know how to get it.

We'll play with strings here, making up variable names along the way <g

nextStartAddress = Range("F10").End(xlDown).Offset(3, 0).Address
nextEndAddress = Range(nextStartAddress).end(xldown).Address
now you have the two addresses you need
(let's see if I can build formula properly this time!)
Range("G6").Formula = "=SUM(" & nextStartAddress & ":" & nextEndAddress & ")"

You can play this chase the tail thing all the way down the column. Hard
part might be in deciding when you run out of groups. You can set something
like veryLastRow = Range("F" & Rows.Count).End(xlUP).Row
and test any new point found's .Row property to see if it is greater than
veryLastRow and it if is, you know you are out of data to work with.

"David B" wrote:

Excellent!

now, I need to select the next batch of data in Column "F" (which starts 3
rows below the end of the data in the previous formula) and put the sum in
cell G6.

I think I can make this work...but all input is welcomed!

"Dave Peterson" wrote:

Range("G2").Formula = "=SUM(F10:" & Range("F10").End(xlDown).Address & ")"



David B wrote:

Thanks for your reply:

This code:
Range("G2").Formula = "=SUM(F10:" & Range("F10").End(xlDown).Address

looks to be very close to what I'm attempting, but there seems to be either
quotes or parentheses missing that I can't figure out.

"JLatham" wrote:

David, experiment in VB with the .End method. Examples of its use a

varLastRow = Range("F10").End(xlDown).Row
varLastAddress = Range("F10").End(xlDown).Address
or try this
Range("G2").Formula = "=SUM(F10:" & Range("F10").End(xlDown).Address

the .End takes one of 4 parameters: xlUp, xlDown, xlToLeft or xlToRight

You'll see this kind of thing used a lot also:
longLastUsedRow = Range("A" & Rows.Count).End(xlUp).Row
which will return the last used cell row number in column A. Assuming that
the last cell in column A (A65536 in Excel 2003) is empty, it would return
the row number of the first cell above it that was not empty.

"David B" wrote:

I'm new to VBA Excel and need some help creating some code.

I want to sum two columns of data; the number of rows with data is variable.

First sum=: cell F10 through the end of the range (Which I would normally
select by using shift/ctrl/down arrow). The Sum of this range needs to be in
cell G2.

Second Sum: The second sum needs to be the total of the range that starts 3
rows below (i.e. 2 blank rows) through the end of the data in that column
(Again, I use shift/ctrl/down arrow). This sum needs to be in cell G6.

Am having a tough time writing the code correctly to select the two
ranges....thanks for any hints you can provide.

--

Dave Peterson