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

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.


"Stav19" wrote:

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

On Jul 29, 2:01*pm, John Bundy (remove) wrote:
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.



"Stav19" wrote:
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- Hide quoted text -

- 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?

On Jul 29, 2:34*pm, Stav19 wrote:
On Jul 29, 2:01*pm, John Bundy (remove) wrote:





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.

"Stav19" wrote:
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- Hide quoted text -

- 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?- 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!

On Jul 29, 2:44*pm, Stav19 wrote:
On Jul 29, 2:34*pm, Stav19 wrote:





On Jul 29, 2:01*pm, John Bundy (remove) wrote:

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.

"Stav19" wrote:
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- Hide quoted text -

- 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?- 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!- 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?

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

On Jul 30, 2:09*am, "kounoike" wrote:
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" wrote in message

...



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- Hide quoted text -

- 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

"Stav19" wrote in message
...
On Jul 30, 2:09 am, "kounoike" wrote:
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" wrote in message

...



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- Hide quoted text -

- 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

On Jul 30, 11:03*am, "kounoike" wrote:
"Stav19" wrote in message

...
On Jul 30, 2:09 am, "kounoike" wrote:





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

...

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- Hide quoted text -

- 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- 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!

"Stav19" wrote in message
...
On Jul 30, 11:03 am, "kounoike" wrote:

--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