Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Copy
i have a bunch of military times that a user is typing in under the W column starting in cell 9. The user then hits a macro button that will take all the times they punched in and copy each one 24 times under the B column starting with cell 9. for example: 1432 is typed into Cell W9. 1432 gets copied into cell B9-B32. 1526 is typed into Cell W10. 1526 gets copied into cell B33-B56. Here is my macro code: Dim copyfield Dim pastefield1 Dim pastefield2 For counter = 9 To 200 copyfield = "W" & counter pastefield1 = (counter - 9) * 24 + 9 pastefield2 = pastefield1 + 23 pastefield1 = "B" & pastefield1 pastefield2 = "B" & pastefield2 Worksheets("Sheet1").Range(copyfield).Copy _ Destination:=Worksheets("Sheet1").Range(pastefield 1, pastefield2) Next counter End Sub I keep getting the error "Runtime error 9. Subscript out of range" Can anyone help me out why? thanks for your help -timrekdgorf ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Copy
Works for me
Neil "timrekdgorf" wrote in message ... i have a bunch of military times that a user is typing in under the W column starting in cell 9. The user then hits a macro button that will take all the times they punched in and copy each one 24 times under the B column starting with cell 9. for example: 1432 is typed into Cell W9. 1432 gets copied into cell B9-B32. 1526 is typed into Cell W10. 1526 gets copied into cell B33-B56. Here is my macro code: Dim copyfield Dim pastefield1 Dim pastefield2 For counter = 9 To 200 copyfield = "W" & counter pastefield1 = (counter - 9) * 24 + 9 pastefield2 = pastefield1 + 23 pastefield1 = "B" & pastefield1 pastefield2 = "B" & pastefield2 Worksheets("Sheet1").Range(copyfield).Copy _ Destination:=Worksheets("Sheet1").Range(pastefield 1, pastefield2) Next counter End Sub I keep getting the error "Runtime error 9. Subscript out of range" Can anyone help me out why? thanks for your help -timrekdgorf ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Copy
One way:
Sub CopyTimes() Const cOffset = 24 Dim vOffset As Integer Dim Cell As Range Application.ScreenUpdating = False ' for testing, set some source fields Range("W9") = 1432 Range("W10") = 1526 Range("W11") = 1933 Range("W12") = 2042 ' for each value, copy and autofill down For Each Cell In Range(Range("W9"), Range("W9").End(xlDown)) Cell.Copy Range("B9").Offset(vOffset, 0) Range("B9").Offset(vOffset, 0).AutoFill _ Range(Range("B9").Offset(vOffset, 0), _ Range("B9").Offset(vOffset + 23, 0)) vOffset = vOffset + cOffset Next 'Cell Application.ScreenUpdating = True End Sub Regards Trevor "timrekdgorf" wrote in message ... i have a bunch of military times that a user is typing in under the W column starting in cell 9. The user then hits a macro button that will take all the times they punched in and copy each one 24 times under the B column starting with cell 9. for example: 1432 is typed into Cell W9. 1432 gets copied into cell B9-B32. 1526 is typed into Cell W10. 1526 gets copied into cell B33-B56. Here is my macro code: Dim copyfield Dim pastefield1 Dim pastefield2 For counter = 9 To 200 copyfield = "W" & counter pastefield1 = (counter - 9) * 24 + 9 pastefield2 = pastefield1 + 23 pastefield1 = "B" & pastefield1 pastefield2 = "B" & pastefield2 Worksheets("Sheet1").Range(copyfield).Copy _ Destination:=Worksheets("Sheet1").Range(pastefield 1, pastefield2) Next counter End Sub I keep getting the error "Runtime error 9. Subscript out of range" Can anyone help me out why? thanks for your help -timrekdgorf ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Copy
Actually, it worked for me too. The version I posted might be a bit quicker
given that it only copies the number of cells entered rather than just looping between 9 and 200. Of course , if all those cells are filled it won't make a whole lot of difference. Sub test() Dim copyfield Dim pastefield1 Dim pastefield2 Dim counter Range("W9") = 1432 Range("W10") = 1526 Range("W11") = 1933 Range("W12") = 2042 For counter = 9 To 200 copyfield = "W" & counter pastefield1 = (counter - 9) * 24 + 9 pastefield2 = pastefield1 + 23 pastefield1 = "B" & pastefield1 pastefield2 = "B" & pastefield2 Worksheets("Sheet1").Range(copyfield).Copy _ Destination:=Worksheets("Sheet1").Range(pastefield 1, pastefield2) Next counter End Sub Regards Trevor "Neil" wrote in message ... Works for me Neil "timrekdgorf" wrote in message ... i have a bunch of military times that a user is typing in under the W column starting in cell 9. The user then hits a macro button that will take all the times they punched in and copy each one 24 times under the B column starting with cell 9. for example: 1432 is typed into Cell W9. 1432 gets copied into cell B9-B32. 1526 is typed into Cell W10. 1526 gets copied into cell B33-B56. Here is my macro code: Dim copyfield Dim pastefield1 Dim pastefield2 For counter = 9 To 200 copyfield = "W" & counter pastefield1 = (counter - 9) * 24 + 9 pastefield2 = pastefield1 + 23 pastefield1 = "B" & pastefield1 pastefield2 = "B" & pastefield2 Worksheets("Sheet1").Range(copyfield).Copy _ Destination:=Worksheets("Sheet1").Range(pastefield 1, pastefield2) Next counter End Sub I keep getting the error "Runtime error 9. Subscript out of range" Can anyone help me out why? thanks for your help -timrekdgorf ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Copy
Expanding on your excellent idea might be the use of "Resize."
Sub Demo() Dim Cell As Range ' Small test data [W9:W12] = [Transpose({1432,1526,1933,2042})] Application.ScreenUpdating = False For Each Cell In Range([W9], [W9].End(xlDown)) Cells(24 * Cell.Row - 207, 2).Resize(24, 1) = Cell Next Application.ScreenUpdating = True End Sub -- Dana DeLouis Using Windows XP & Office XP = = = = = = = = = = = = = = = = = "Trevor Shuttleworth" wrote in message ... One way: Sub CopyTimes() Const cOffset = 24 Dim vOffset As Integer Dim Cell As Range Application.ScreenUpdating = False ' for testing, set some source fields Range("W9") = 1432 Range("W10") = 1526 Range("W11") = 1933 Range("W12") = 2042 ' for each value, copy and autofill down For Each Cell In Range(Range("W9"), Range("W9").End(xlDown)) Cell.Copy Range("B9").Offset(vOffset, 0) Range("B9").Offset(vOffset, 0).AutoFill _ Range(Range("B9").Offset(vOffset, 0), _ Range("B9").Offset(vOffset + 23, 0)) vOffset = vOffset + cOffset Next 'Cell Application.ScreenUpdating = True End Sub Regards Trevor "timrekdgorf" wrote in message ... i have a bunch of military times that a user is typing in under the W column starting in cell 9. The user then hits a macro button that will take all the times they punched in and copy each one 24 times under the B column starting with cell 9. for example: 1432 is typed into Cell W9. 1432 gets copied into cell B9-B32. 1526 is typed into Cell W10. 1526 gets copied into cell B33-B56. Here is my macro code: Dim copyfield Dim pastefield1 Dim pastefield2 For counter = 9 To 200 copyfield = "W" & counter pastefield1 = (counter - 9) * 24 + 9 pastefield2 = pastefield1 + 23 pastefield1 = "B" & pastefield1 pastefield2 = "B" & pastefield2 Worksheets("Sheet1").Range(copyfield).Copy _ Destination:=Worksheets("Sheet1").Range(pastefield 1, pastefield2) Next counter End Sub I keep getting the error "Runtime error 9. Subscript out of range" Can anyone help me out why? thanks for your help -timrekdgorf ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2000 copy, edit, paste spacial, value. How Excel 2007 ?? | Excel Discussion (Misc queries) | |||
copy drop down lists from desktop excel to pocket excel | Excel Discussion (Misc queries) | |||
Copy/Paste how to avoid the copy of formula cells w/o calc values | Excel Discussion (Misc queries) | |||
EXCEL FILE a copy/a copy/a copy ....filename | New Users to Excel | |||
copy & paste spreadsheet cells from excel to outlook to excel | Excel Discussion (Misc queries) |