On Feb 3, 10:55*am, "Roger Govier"
<roger@technology4unospamdotcodotuk wrote:
Hi

You are missing a lot of End If statements.
In one case it is omitted altogether, in other case you just have End.
Setting DataSht is incorrect

The following compiles OK

Sub SumHKCdays()
* * Dim DataSht As Worksheet
* * Dim Start As Double
* * Dim LstRow As Long
* * Dim RngHD As Range
* * Dim RngKD As Range
* * Dim RngCD As Range
* * Dim Rng As Range
* * Dim i
* * Dim Dt As Double
* * Dim X As Double
* * Dim Target As Range
* * Set Target = Range("H3")
* * Set DataSht = Sheets("Sheet22")
* * LstRow = DataSht.Range("A1"). _
* * SpecialCells(xlCellTypeLastCell).Row
* * Set RngHD = DataSht.Range("A5:A" & LstRow)
* * Set RngKD = DataSht.Range("BC5:BC" & LstRow)
* * Set RngCD = DataSht.Range("CD5:CD" & LstRow)

* * Do Until Target.Roww = 32
* * * * If Target.Offset(0, -7) = "" Then
* * * * * * i = 0
* * * * Else
* * * * * * Start = Target.Offset(0, -7) - 13
* * * * * * Dt = Target.Offset(0, -7)
* * * * End If
* * * * For Each i In RngHD
* * * * * * If i <= Dt And i = Start Then
* * * * * * * * X = X + i.Offset(0, 1) + i.Offset(0, 6) + i.Offset(0, 11) _
* * * * * * * * * * + i.Offset(0, 16) + i.Offset(0, 21) + i.Offset(0, 26) _
* * * * * * * * * * + i.Offset(0, 31) + i.Offset(0, 36) + i.Offset(0, 43) _
* * * * * * * * * * + i.Offset(0, 48)
* * * * * * Else
* * * * * * * * X = X + 0
* * * * * * End If
* * * * Next i

* * * * For Each i In RngKD
* * * * * * If i <= Dt And i = Start Then
* * * * * * * * X = X + i.Offset(0, 1) + i.Offset(0, 6) + i.Offset(0, 11) _
* * * * * * * * * * + i.Offset(0, 16) + i.Offset(0, 21) + i.Offset(0, 24)
* * * * * * Else
* * * * * * * * X = X + 0
* * * * * * End If
* * * * Next i
* * * * For Each i In RngCD
* * * * * * If i <= Dt And i = Start Then
* * * * * * * * X = X + i.Offset(0, 1) + i.Offset(0, 4) + i.Offset(0, 7) _
* * * * * * * * * * + i.Offset(0, 12)
* * * * * * Else
* * * * * * * * X = X + 0
* * * * * * End If
* * * * Next i

* * * * i = X
* * Loop

End Sub

--
Regards
Roger Govier

"jlclyde" wrote in message

...



My code is below. *It keeps throwing an error on the fist for each.
It says Next without for. *Any help will be greatly appreciated.

Thanks,
Jay

Sub SumHKCdays()
* *Dim DataSht As Worksheet
* *Dim Start As Double
* *Dim LstRow As Long
* *Dim RngHD As Range
* *Dim RngKD As Range
* *Dim RngCD As Range
* *Dim Rng As Range
* *Dim i
* *Dim Dt As Double
* *Dim X As Double
* *Dim Target As Range
* *Set Target = Range("H3")
* *Set DataSht = Sheet22
* *LstRow = DataSht.Range("A1").SpecialCells(xlCellTypeLastCel l).Row
* *Set RngHD = DataSht.Range("A5:A" & LstRow)
* *Set RngKD = DataSht.Range("BC5:BC" & LstRow)
* *Set RngCD = DataSht.Range("CD5:CD" & LstRow)

* Do Until Target.Roww = 32
* * * *If Target.Offset(0, -7) = "" Then
* * * * * *i = 0
* * * *Else
* * * *Start = Target.Offset(0, -7) - 13
* * * *Dt = Target.Offset(0, -7)

* *For Each i In RngHD
* * * *If i <= Dt And i = Start Then
* * * * * *X = X + i.Offset(0, 1) + i.Offset(0, 6) + i.Offset(0, 11)
+ _
* * * * * *i.Offset(0, 16) + i.Offset(0, 21) + i.Offset(0, 26) + _
* * * * * *i.Offset(0, 31) + i.Offset(0, 36) + i.Offset(0, 43) +
i.Offset(0, 48)
* * * *Else
* * * * * *X = X + 0
* * * *End
* *Next i

* *For Each i In RngKD
* * * *If i <= Dt And i = Start Then
* * * * * *X = X + i.Offset(0, 1) + i.Offset(0, 6) + i.Offset(0, 11)
+ _
* * * * * *i.Offset(0, 16) + i.Offset(0, 21) + i.Offset(0, 24)
* * * *Else
* * * * * *X = X + 0
* * * *End
* *Next i
* * * *For Each i In RngCD
* * * *If i <= Dt And i = Start Then
* * * * * *X = X + i.Offset(0, 1) + i.Offset(0, 4) + i.Offset(0, 7) +
_
* * * * * *i.Offset(0, 12)
* * * *Else
* * * * * *X = X + 0
* * * *End
* *Next i
* *End If
* *i = X
* *Loop

End Sub- Hide quoted text -

- Show quoted text -

Setting the sheet works fine. The sheets name is not 22 it is the
code name that never changes. I really screwed up the End ifs though.

Thanks for the help,
Jay