Syntax error on codes
 

thanks a million Tom. I am getting syntax error on the
modified codes, which is pointing at this statement:

& _
",""Vacation""," & rng1.Offset(0,
4).Address & ")"

and

& _
",""Sick""," & rng1.Offset(0,
4).Address & ")"

thanks again

Syntax error on codes
 

mary,

Not sure what code that Tom gave you (it's always better
to keep your posts in the original thread).

I suspect the problem is wordwrap.
Sometimes code posted in the ng gets wrapped (cuts off and starts
a new line at inappropriate places).

Take Tom's original code and repaste it.
Where the lines show up in red, at the end of the first red line, keep
pressing delete until it unwraps. Whenever you run into a "_", delete it too
until the code is all on one line.

John

Whatever was before the
"mary" wrote in message
...
thanks a million Tom. I am getting syntax error on the
modified codes, which is pointing at this statement:

& _
",""Vacation""," & rng1.Offset(0,
4).Address & ")"

and

& _
",""Sick""," & rng1.Offset(0,
4).Address & ")"

thanks again

Syntax error on codes
 

mary,

From the original code that Tom gave you:

c.Offset(1, 1).Formula = "=Sumif(" & rng1.Offset(0,
2).Address
& _

The above is all one line.

John


"mary" wrote in message
...
thanks a million Tom. I am getting syntax error on the
modified codes, which is pointing at this statement:

& _
",""Vacation""," & rng1.Offset(0,
4).Address & ")"

and

& _
",""Sick""," & rng1.Offset(0,
4).Address & ")"

thanks again

Syntax error on codes
 

This is the code:
Sub ProcessData()
Dim cnt As Long, cnt1 As Long
Dim c As Range
Dim firstAddress As String
Dim rngStart As Range

With Worksheets(1).Columns(1)
Set rngStart = .Cells(1, 1)
Set c = .Find("Total", _
After:=Worksheets(1).Cells(Rows.Count, 1), _
Lookat:=xlPart, LookIn:=xlValues, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If Not c Is Nothing Then
cnt = 1
firstAddress = c.Address
Do
If cnt Mod 5 = 0 Then
cnt1 = Application.Round(cnt / 5, 0)
c.Offset(1, 0).Resize(5).EntireRow.Insert
c.Value = "Total" & cnt1
c.Offset(1, 0).Value = "Vacation" & cnt1
c.Offset(2, 0).Value = "Sick" & cnt1
Set rng1 = Worksheets(1).Range(rngStart,
c.Offset(-1, 0))
c.Offset(1, 1).Formula = "=Sumif(" &
rng1.Offset(0, 2).Address
& _
",""Vacation""," & rng1.Offset(0,
4).Address & ")"
c.Offset(1, 1).BorderAround Weight:=xlMedium
c.Offset(2, 1).Formula = "=Sumif(" &
rng1.Offset(0, 2).Address
& _
",""Sick""," & rng1.Offset(0,
4).Address & ")"
c.Offset(2, 1).BorderAround Weight:=xlMedium
Set rngStart = c.Offset(1, 0)
End If
Set c = .FindNext(c)
cnt = cnt + 1
Loop While Not c Is Nothing And c.Address <
firstAddress
End If
End With
End Sub

-----Original Message-----
mary,

Not sure what code that Tom gave you (it's always better
to keep your posts in the original thread).

I suspect the problem is wordwrap.
Sometimes code posted in the ng gets wrapped (cuts off
and starts
a new line at inappropriate places).

Take Tom's original code and repaste it.
Where the lines show up in red, at the end of the first
red line, keep
pressing delete until it unwraps. Whenever you run into
a "_", delete it too
until the code is all on one line.

John

Whatever was before the
"mary" wrote in message
...
thanks a million Tom. I am getting syntax error on the
modified codes, which is pointing at this statement:

& _
",""Vacation""," & rng1.Offset(0,
4).Address & ")"

and

& _
",""Sick""," & rng1.Offset(0,
4).Address & ")"

thanks again


.