Syntax error on codes

  • Thread starter Thread starter mary
  • Start date Start date
M

mary

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

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