Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Macro to add Multiple columns
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 410
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 410
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 772
Default 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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 772
Default 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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 2,480
Default 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

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 410
Default 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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
I need a macro to transpose multiple columns A1-Z1, A2-X2 etc Wombat62 Excel Discussion (Misc queries) 7 September 18th 06 05:05 AM
Combine multiple columns into two long columns, Repeating rows in first column [email protected] Excel Discussion (Misc queries) 2 July 31st 06 09:45 PM
Combine multiple columns into two long columns, Repeating rows in first column [email protected] Excel Discussion (Misc queries) 0 July 31st 06 05:07 PM
macro to compile columns on multiple sheets simonsmith Excel Discussion (Misc queries) 2 May 9th 06 04:06 PM
Macro to align and compare multiple rows and columns Manav Ram via OfficeKB.com New Users to Excel 1 March 5th 05 12:38 AM


All times are GMT +1. The time now is 06:47 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement