Summing a dynamic range between two points

[Stav19]

Hi All

what I'm trying to do is in column C at certain points insert a total
of a range of cells between a number of points in column B based on
what's in columns A and B if that makes sense!

So far i have the following code which works to a point, but i'm
getting stuck on the sum as I'm trying to sum up between two "(%)" 's
if that makes sense:

Column A Column B
Points (%)
Jan 2
Feb 1
Mar 3
Apr 4
May 5
June 1 Select here and insert a
formula to total 16
Point 1 (%)

Sub InsertCheck()

Dim x
Sheets("Values").Select
Range("A1").Select

x = 0
Do Until ActiveCell = "xxx"
If ActiveCell.Value = "Point 1" Then
ActiveCell.Offset(-1, 2).Select
x = x + 1
Else
ActiveCell.Offset(1, 0).Select
End If
Loop

End Sub

This enables me to select the cell on the same row as june, but I'm
struggling to insert the sum using a dynamic range, as I want to total
between the two Percentage signs if that makes sense...

Can anyone help?

thanks in advance

 

[John Bundy]

Subtotal along the way with a variable, something like this

Sub InsertCheck()

Dim x
Dim lngTotal as Long

lngTotal=0
Sheets("Values").Select
Range("A1").Select

x = 0
Do Until ActiveCell = "xxx"
If ActiveCell.Value = "Point 1" Then
ActiveCell.Offset(-1, 2)=lngTotal
x = x + 1
Else
lngTotal=lngTotal + ActiveCell.Offset(0, 1).value
ActiveCell.Offset(1, 0).Select
End If
Loop

End Sub


'untested but you should get the point, if not, post!

 

[Stav19]

Subtotal along the way with a variable, something like this

Sub InsertCheck()

Dim x
Dim lngTotal as Long

lngTotal=0
Sheets("Values").Select
Range("A1").Select

x = 0
Do Until ActiveCell = "xxx"
    If ActiveCell.Value = "Point 1" Then
        ActiveCell.Offset(-1, 2)=lngTotal
   x = x + 1
    Else
lngTotal=lngTotal + ActiveCell.Offset(0, 1).value
        ActiveCell.Offset(1, 0).Select
End If
Loop

End Sub

'untested but you should get the point, if not, post!
--
-John
Please rate when your question is answered to help us and others know what
is helpful.















- Show quoted text -

Thanks for the reply John, that kind of worked, I had to change the
range to "A2" and the total I got (I am using different figures) was
round up, I need to work to 2 dp.

Also is there any way to show the formula in the selected cell?

 

[Stav19]

Thanks for the reply John, that kind of worked, I had to change the
range to "A2" and the total I got (I am using different figures) was
round up, I need to work to 2 dp.

Also is there any way to show the formula in the selected cell?- Hide quoted text -

- Show quoted text -

I also think I've got something wrong with my original code, as it
doesn't stop, trying to run before i can walk, damnit!

 

[Stav19]

I also think I've got something wrong with my original code, as it
doesn't stop, trying to run before i can walk, damnit!- Hide quoted text -

- Show quoted text -

I've changed the classification of the variable to single, and the
code is still running as I have "xxx" at the bottom of the
spreadsheet, does anyone know how I can show the sum formula in the
selected cell?

 

[kounoike]

Try this one.

Sub InsertChecktest()
Dim firstcell As Range, secondcell As Range
Dim lastcell As Range

Set lastcell = Range("B1").End(xlDown)

With Columns("B")
.Offset(0, 1).ClearContents

Set firstcell = .Find("(%)", after:=lastcell, LookIn:=xlValues)

If firstcell Is Nothing Then
Exit Sub
Else
Do
Set secondcell = .Find("(%)", after:=firstcell, LookIn:=xlValues)
If secondcell Is Nothing Then
lastcell.Offset(0, 1).Formula = "=sum(" & Range _
(firstcell(2, 1), lastcell(0, 1)).Address(False, False) & ")"
Exit Sub
ElseIf firstcell.Row >= secondcell.Row Then
lastcell.Offset(0, 1).Formula = "=sum(" & Range _
(firstcell(2, 1), lastcell).Address(False, False) & ")"
Exit Do
Else
secondcell.Offset(0, 1).Formula = "=sum(" & Range _
(firstcell(2, 1), secondcell(0, 1)).Address(False, False) & ")"
End If
Set firstcell = secondcell
Loop
End If
End With
End Sub

keiji

 

[Stav19]

Try this one.

Sub InsertChecktest()
Dim firstcell As Range, secondcell As Range
Dim lastcell As Range

Set lastcell = Range("B1").End(xlDown)

With Columns("B")
    .Offset(0, 1).ClearContents

Set firstcell = .Find("(%)", after:=lastcell, LookIn:=xlValues)

If firstcell Is Nothing Then
    Exit Sub
Else
    Do
        Set secondcell = .Find("(%)", after:=firstcell, LookIn:=xlValues)
        If secondcell Is Nothing Then
            lastcell.Offset(0, 1).Formula = "=sum(" & Range _
            (firstcell(2, 1), lastcell(0, 1)).Address(False, False) & ")"
            Exit Sub
        ElseIf firstcell.Row >= secondcell.Row Then
            lastcell.Offset(0, 1).Formula = "=sum(" & Range _
            (firstcell(2, 1), lastcell).Address(False, False)& ")"
            Exit Do
        Else
            secondcell.Offset(0, 1).Formula = "=sum(" & Range _
            (firstcell(2, 1), secondcell(0, 1)).Address(False, False) & ")"
        End If
        Set firstcell = secondcell
    Loop
End If
End With
End Sub

keiji
















- Show quoted text -

Hi Keiji

thanks for that, unfortunately it says that there's a run time error
as an object is required, I've tried adding in selecting the sheet
it's for, but that doesn't seem to work, any ideas?

thx

 

[kounoike]

Try this one.

Sub InsertChecktest()
Dim firstcell As Range, secondcell As Range
Dim lastcell As Range

Set lastcell = Range("B1").End(xlDown)

With Columns("B")
.Offset(0, 1).ClearContents

Set firstcell = .Find("(%)", after:=lastcell, LookIn:=xlValues)

If firstcell Is Nothing Then
Exit Sub
Else
Do
Set secondcell = .Find("(%)", after:=firstcell, LookIn:=xlValues)
If secondcell Is Nothing Then
lastcell.Offset(0, 1).Formula = "=sum(" & Range _
(firstcell(2, 1), lastcell(0, 1)).Address(False, False) & ")"
Exit Sub
ElseIf firstcell.Row >= secondcell.Row Then
lastcell.Offset(0, 1).Formula = "=sum(" & Range _
(firstcell(2, 1), lastcell).Address(False, False) & ")"
Exit Do
Else
secondcell.Offset(0, 1).Formula = "=sum(" & Range _
(firstcell(2, 1), secondcell(0, 1)).Address(False, False) & ")"
End If
Set firstcell = secondcell
Loop
End If
End With
End Sub

keiji
















- Show quoted text -

Hi Keiji

thanks for that, unfortunately it says that there's a run time error
as an object is required, I've tried adding in selecting the sheet
it's for, but that doesn't seem to work, any ideas?

thx

Hi Stav19

Sorry for incovenience.
But I tested with your date with Excel 2003 and didn't have such problem.
so, i have no idea what was the problem.
Which line have a run time error in my code? Where did you add in code
"select the sheet"?

keiji

 

[Stav19]

Hi Stav19

Sorry for incovenience.
But I tested with your date with Excel 2003 and didn't have such problem.
so, i have no idea what was the problem.
Which line have a run time error in my code? Where did you add in code
"select the sheet"?

keiji- Hide quoted text -

- Show quoted text -

Apologies, I was trying to use what you'd given me in a different
spreadsheet, and it didn't seem to work, but tried the above using my
initial example, and it worked fine, I just need to figure out how to
change it to make it work. Thanks for your help on this!

 

[kounoike]

--snip

- Show quoted text -

Apologies, I was trying to use what you'd given me in a different
spreadsheet, and it didn't seem to work, but tried the above using my
initial example, and it worked fine, I just need to figure out how to
change it to make it work. Thanks for your help on this!

Please show me what you did step by step. espeacially, your sheet's name and
your data layout in that sheet etc.

keiji

 

[Stav19]

--snip


Please show me what you did step by step. espeacially, your sheet's name and
your data layout in that sheet etc.

keiji

The sheet is called "Values" data looks like this in columns A and B:

Point 1 (%)
Asset #.##
Asset #.##
Asset #.##
Point 2 (%)
Asset #.##
Asset #.##
Asset #.##
Money1 £#.##
Money2 £#.##
Money3 £#.##
Money4 £#.##
Money5 £#.##
Money6 £#.##
Point 3 (%)
Country #.##
Country #.##
Country #.##
Point 4 (%)
Type 1 #.##
Type 2 #.##
Type 3 #.##

xxx

Apologies for the size, but I've tried to keep it short, basically in
column C i'd like to sum up the percentages of "points 1-4"
separately, and also the amount of money. Basically in the future,
the amounts of data in each little group will change, I wanted to be
able to ensure the spreadsheet can deal with that.
I appreciate any help you can give me!

 

[kounoike]

--snip


Please show me what you did step by step. espeacially, your sheet's name
and
your data layout in that sheet etc.

keiji

The sheet is called "Values" data looks like this in columns A and B:

Point 1 (%)
Asset #.##
Asset #.##
Asset #.##
Point 2 (%)
Asset #.##
Asset #.##
Asset #.##
Money1 £#.##
Money2 £#.##
Money3 £#.##
Money4 £#.##
Money5 £#.##
Money6 £#.##
Point 3 (%)
Country #.##
Country #.##
Country #.##
Point 4 (%)
Type 1 #.##
Type 2 #.##
Type 3 #.##

Apologies for the size, but I've tried to keep it short, basically in
column C i'd like to sum up the percentages of "points 1-4"
separately, and also the amount of money. Basically in the future,
the amounts of data in each little group will change, I wanted to be
able to ensure the spreadsheet can deal with that.
I appreciate any help you can give me!

Because of my poor English ability, I can't fully understand what you want
to do.
You said in your first post that when you run my macro, there's a run time
error
as an object is required. But i can't think of any causes why that happened,
although, in my thought, it will give you wrong results or it will do
nothing.

Do you want a sum of percentages of "points 1-4" separately in column C and
at the same time, do you want a sum of money, if it exists, of "points 1-4"
separately in column C? In that case, in which position should a sum of
money reside?

I think it's diffcult to know which is percentages and which is money
without any marks like (%) and same sets, in this case percentage and money
should reside continuously. if not, it would be more difficlut to sum up.

Sorry for being without any help.

keiji

 

[Stav19]

Because of  my poor English ability, I can't fully understand what you want
to do.
You said in your first post that when you run my macro, there's a run time
error
as an object is required. But i can't think of any causes why that happened,
although, in my thought, it will give you wrong results or it will do
nothing.

Do you want a sum of percentages of "points 1-4" separately in column C and
at the same time, do you want a sum of money, if it exists, of "points 1-4"
separately in column C? In that case, in which position should a sum of
money reside?

I think it's diffcult to know which is percentages and which is money
without any marks like (%) and same sets, in this case percentage and money
should  reside continuously. if not, it would be more difficlut to sum up.

Sorry for being without any help.

keiji- Hide quoted text -

- Show quoted text -

Hi Keiji

you've already been a great helpm thanks!