ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Macro to add Multiple columns (https://www.excelbanter.com/excel-discussion-misc-queries/218904-macro-add-multiple-columns.html)

jlclyde
Macro to add Multiple columns
 
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

jlclyde
Macro to add Multiple columns
 
On Feb 3, 10:37*am, jlclyde wrote:
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

I thought I had looked this over but I guess I had not. Please do not
repond to this post. End ifs are not in spelling is poor all over the
place. I will post back after I clean it up and if I am still having
problems.

Thanks,
Jay

John Bundy
Macro to add Multiple columns
 
Change your End to End If on all of them. Another error is:
Do Until Target.Roww = 32
Change to Roww.

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"jlclyde" wrote:

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

John Bundy
Macro to add Multiple columns
 
haha even got me doing it, change Roww to Row.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"jlclyde" wrote:

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

Roger Govier[_3_]
Macro to add Multiple columns
 
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


jlclyde
Macro to add Multiple columns
 
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


All times are GMT +1. The time now is 02:26 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com