"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