Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Almost there with procedure error
I have the following program to translate a single-column database into a
matrix with five columns. I get an error on the line: startspot.Offset(1, 0) = Right(startspot.Offset(0, 0).Value, Len(startspot.Value) - 6) I'm not sure what the problem is. I've tried re-arranging and doing some different things, but can't figure it out. Any thoughts? Thanks! Mind text wrapping: Option Explicit Sub Save_Time() Dim glcount, j, m, n As Integer Dim startspot, nextspot As Range j = 0 Set startspot = Range("A1") 'or wherever you start Set nextspot = Range("A1") Do Set startspot = startspot.Offset(j, 0) If nextspot = "" Then Exit Do nextspot.Select ActiveCell.Offset(1, 0).Cut Destination:=startspot.Offset(0, 1) ActiveCell.Offset(0, 0).Cut Destination:=startspot.Offset(0, 0) ActiveCell.Offset(2, 0).Cut Destination:=startspot.Offset(0, 2) startspot.Offset(1, 1).Value = Right(startspot.Offset(0, 1).Value, Len(startspot.Value) - 6) startspot.Offset(1, 0) = Right(startspot.Offset(0, 0).Value, Len(startspot.Value) - 6) startspot.Value = Left(startspot.Value, 4) startspot.Offset(0, 1).Value = Left(startspot.Offset(0, 1).Value, 4) startspot.Offset(1, 2).Value = Right(startspot.Offset(0, 2).Value, Len(startspot.Offset(0, 2).Value) - 7) startspot.Offset(0, 2).Value = Left(startspot.Offset(0, 2).Value, 5) Set nextspot = ActiveCell.Offset(4, 0) nextspot.Select Set nextspot = ActiveCell.End(xlDown).Offset(2, 0) With ActiveCell m = Range(.Offset(0, 0), .End(xlDown)).Rows.Count End With ActiveCell.Resize(m, 1).Select Selection.Cut Destination:=startspot.Offset(0, 3) nextspot.Select Set nextspot = ActiveCell.End(xlDown).Offset(2, 0) With ActiveCell n = Range(.Offset(0, 0), .End(xlDown)).Rows.Count End With ActiveCell.Resize(n, 1).Select Selection.Cut Destination:=startspot.Offset(0, 4) j = j + Application.Max(n, m) Loop End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Almost there with procedure error
In case it helps, the single line database looks like the following:
Date: 123 Name: 456 Shift: 789 Category A line 1 line 2 line 3 line 4 line 5 Category B line 1 line 2 line 3 line 4 line 5 line 6 Date: 123 Name: 456 Shift: 789 Category A line 1 line 2 line 3 line 4 line 5 Category B line 1 line 2 line 3 line 4 line 5 line 6 "StumpedAgain" wrote: I have the following program to translate a single-column database into a matrix with five columns. I get an error on the line: startspot.Offset(1, 0) = Right(startspot.Offset(0, 0).Value, Len(startspot.Value) - 6) I'm not sure what the problem is. I've tried re-arranging and doing some different things, but can't figure it out. Any thoughts? Thanks! Mind text wrapping: Option Explicit Sub Save_Time() Dim glcount, j, m, n As Integer Dim startspot, nextspot As Range j = 0 Set startspot = Range("A1") 'or wherever you start Set nextspot = Range("A1") Do Set startspot = startspot.Offset(j, 0) If nextspot = "" Then Exit Do nextspot.Select ActiveCell.Offset(1, 0).Cut Destination:=startspot.Offset(0, 1) ActiveCell.Offset(0, 0).Cut Destination:=startspot.Offset(0, 0) ActiveCell.Offset(2, 0).Cut Destination:=startspot.Offset(0, 2) startspot.Offset(1, 1).Value = Right(startspot.Offset(0, 1).Value, Len(startspot.Value) - 6) startspot.Offset(1, 0) = Right(startspot.Offset(0, 0).Value, Len(startspot.Value) - 6) startspot.Value = Left(startspot.Value, 4) startspot.Offset(0, 1).Value = Left(startspot.Offset(0, 1).Value, 4) startspot.Offset(1, 2).Value = Right(startspot.Offset(0, 2).Value, Len(startspot.Offset(0, 2).Value) - 7) startspot.Offset(0, 2).Value = Left(startspot.Offset(0, 2).Value, 5) Set nextspot = ActiveCell.Offset(4, 0) nextspot.Select Set nextspot = ActiveCell.End(xlDown).Offset(2, 0) With ActiveCell m = Range(.Offset(0, 0), .End(xlDown)).Rows.Count End With ActiveCell.Resize(m, 1).Select Selection.Cut Destination:=startspot.Offset(0, 3) nextspot.Select Set nextspot = ActiveCell.End(xlDown).Offset(2, 0) With ActiveCell n = Range(.Offset(0, 0), .End(xlDown)).Rows.Count End With ActiveCell.Resize(n, 1).Select Selection.Cut Destination:=startspot.Offset(0, 4) j = j + Application.Max(n, m) Loop End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Almost there with procedure error
How long is the value in startspot when the code blows up?
What is in that cell? StumpedAgain wrote: I have the following program to translate a single-column database into a matrix with five columns. I get an error on the line: startspot.Offset(1, 0) = Right(startspot.Offset(0, 0).Value, Len(startspot.Value) - 6) I'm not sure what the problem is. I've tried re-arranging and doing some different things, but can't figure it out. Any thoughts? Thanks! Mind text wrapping: Option Explicit Sub Save_Time() Dim glcount, j, m, n As Integer Dim startspot, nextspot As Range j = 0 Set startspot = Range("A1") 'or wherever you start Set nextspot = Range("A1") Do Set startspot = startspot.Offset(j, 0) If nextspot = "" Then Exit Do nextspot.Select ActiveCell.Offset(1, 0).Cut Destination:=startspot.Offset(0, 1) ActiveCell.Offset(0, 0).Cut Destination:=startspot.Offset(0, 0) ActiveCell.Offset(2, 0).Cut Destination:=startspot.Offset(0, 2) startspot.Offset(1, 1).Value = Right(startspot.Offset(0, 1).Value, Len(startspot.Value) - 6) startspot.Offset(1, 0) = Right(startspot.Offset(0, 0).Value, Len(startspot.Value) - 6) startspot.Value = Left(startspot.Value, 4) startspot.Offset(0, 1).Value = Left(startspot.Offset(0, 1).Value, 4) startspot.Offset(1, 2).Value = Right(startspot.Offset(0, 2).Value, Len(startspot.Offset(0, 2).Value) - 7) startspot.Offset(0, 2).Value = Left(startspot.Offset(0, 2).Value, 5) Set nextspot = ActiveCell.Offset(4, 0) nextspot.Select Set nextspot = ActiveCell.End(xlDown).Offset(2, 0) With ActiveCell m = Range(.Offset(0, 0), .End(xlDown)).Rows.Count End With ActiveCell.Resize(m, 1).Select Selection.Cut Destination:=startspot.Offset(0, 3) nextspot.Select Set nextspot = ActiveCell.End(xlDown).Offset(2, 0) With ActiveCell n = Range(.Offset(0, 0), .End(xlDown)).Rows.Count End With ActiveCell.Resize(n, 1).Select Selection.Cut Destination:=startspot.Offset(0, 4) j = j + Application.Max(n, m) Loop End Sub -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Almost there with procedure error
The value in startspot is 10 characters long at that point. So I don't think
it's a length error. I took care of those ones. Any other thoughts? "Dave Peterson" wrote: How long is the value in startspot when the code blows up? What is in that cell? StumpedAgain wrote: I have the following program to translate a single-column database into a matrix with five columns. I get an error on the line: startspot.Offset(1, 0) = Right(startspot.Offset(0, 0).Value, Len(startspot.Value) - 6) I'm not sure what the problem is. I've tried re-arranging and doing some different things, but can't figure it out. Any thoughts? Thanks! Mind text wrapping: Option Explicit Sub Save_Time() Dim glcount, j, m, n As Integer Dim startspot, nextspot As Range j = 0 Set startspot = Range("A1") 'or wherever you start Set nextspot = Range("A1") Do Set startspot = startspot.Offset(j, 0) If nextspot = "" Then Exit Do nextspot.Select ActiveCell.Offset(1, 0).Cut Destination:=startspot.Offset(0, 1) ActiveCell.Offset(0, 0).Cut Destination:=startspot.Offset(0, 0) ActiveCell.Offset(2, 0).Cut Destination:=startspot.Offset(0, 2) startspot.Offset(1, 1).Value = Right(startspot.Offset(0, 1).Value, Len(startspot.Value) - 6) startspot.Offset(1, 0) = Right(startspot.Offset(0, 0).Value, Len(startspot.Value) - 6) startspot.Value = Left(startspot.Value, 4) startspot.Offset(0, 1).Value = Left(startspot.Offset(0, 1).Value, 4) startspot.Offset(1, 2).Value = Right(startspot.Offset(0, 2).Value, Len(startspot.Offset(0, 2).Value) - 7) startspot.Offset(0, 2).Value = Left(startspot.Offset(0, 2).Value, 5) Set nextspot = ActiveCell.Offset(4, 0) nextspot.Select Set nextspot = ActiveCell.End(xlDown).Offset(2, 0) With ActiveCell m = Range(.Offset(0, 0), .End(xlDown)).Rows.Count End With ActiveCell.Resize(m, 1).Select Selection.Cut Destination:=startspot.Offset(0, 3) nextspot.Select Set nextspot = ActiveCell.End(xlDown).Offset(2, 0) With ActiveCell n = Range(.Offset(0, 0), .End(xlDown)).Rows.Count End With ActiveCell.Resize(n, 1).Select Selection.Cut Destination:=startspot.Offset(0, 4) j = j + Application.Max(n, m) Loop End Sub -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Almost there with procedure error
What error do you get?
StumpedAgain wrote: The value in startspot is 10 characters long at that point. So I don't think it's a length error. I took care of those ones. Any other thoughts? "Dave Peterson" wrote: How long is the value in startspot when the code blows up? What is in that cell? StumpedAgain wrote: I have the following program to translate a single-column database into a matrix with five columns. I get an error on the line: startspot.Offset(1, 0) = Right(startspot.Offset(0, 0).Value, Len(startspot.Value) - 6) I'm not sure what the problem is. I've tried re-arranging and doing some different things, but can't figure it out. Any thoughts? Thanks! Mind text wrapping: Option Explicit Sub Save_Time() Dim glcount, j, m, n As Integer Dim startspot, nextspot As Range j = 0 Set startspot = Range("A1") 'or wherever you start Set nextspot = Range("A1") Do Set startspot = startspot.Offset(j, 0) If nextspot = "" Then Exit Do nextspot.Select ActiveCell.Offset(1, 0).Cut Destination:=startspot.Offset(0, 1) ActiveCell.Offset(0, 0).Cut Destination:=startspot.Offset(0, 0) ActiveCell.Offset(2, 0).Cut Destination:=startspot.Offset(0, 2) startspot.Offset(1, 1).Value = Right(startspot.Offset(0, 1).Value, Len(startspot.Value) - 6) startspot.Offset(1, 0) = Right(startspot.Offset(0, 0).Value, Len(startspot.Value) - 6) startspot.Value = Left(startspot.Value, 4) startspot.Offset(0, 1).Value = Left(startspot.Offset(0, 1).Value, 4) startspot.Offset(1, 2).Value = Right(startspot.Offset(0, 2).Value, Len(startspot.Offset(0, 2).Value) - 7) startspot.Offset(0, 2).Value = Left(startspot.Offset(0, 2).Value, 5) Set nextspot = ActiveCell.Offset(4, 0) nextspot.Select Set nextspot = ActiveCell.End(xlDown).Offset(2, 0) With ActiveCell m = Range(.Offset(0, 0), .End(xlDown)).Rows.Count End With ActiveCell.Resize(m, 1).Select Selection.Cut Destination:=startspot.Offset(0, 3) nextspot.Select Set nextspot = ActiveCell.End(xlDown).Offset(2, 0) With ActiveCell n = Range(.Offset(0, 0), .End(xlDown)).Rows.Count End With ActiveCell.Resize(n, 1).Select Selection.Cut Destination:=startspot.Offset(0, 4) j = j + Application.Max(n, m) Loop End Sub -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Almost there with procedure error
I get a run-time error '424': Object required on the line:
ActiveCell.Offset(2, 0).Cut Destination:=startspot.Offset(0, 2) in the following section. If I change the order and put the ActiveCell.Offset(2,0).cut line down a couple lines, I get the error on the ActiveCell.Offset(0,0).cut line leading me to believe that I'm somehow confusing the macro. Also, it works the first time through the loop. This error occurs the second time through the loop. Ahh!!! ActiveCell.Offset(1, 0).Cut Destination:=startspot.Offset(0, 1) ActiveCell.Offset(0, 0).Cut Destination:=startspot.Offset(0, 0) ActiveCell.Offset(2, 0).Cut Destination:=startspot.Offset(0, 2) 'error here startspot.Offset(1, 1).Value = Right(startspot.Offset(0, 1).Value, Len(startspot.Value) - 6) startspot.Offset(1, 0) = Right(startspot.Offset(0, 0).Value, Len(startspot.Value) - 6) startspot.Value = Left(startspot.Value, 4) startspot.Offset(0, 1).Value = Left(startspot.Offset(0, 1).Value, 4) startspot.Offset(1, 2).Value = Right(startspot.Offset(0, 2).Value, Len(startspot.Offset(0, 2).Value) - 7) startspot.Offset(0, 2).Value = Left(startspot.Offset(0, 2).Value, 5) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Almost there with procedure error
Instead of guessing at what your code does, how about posting some test data
that's in that single column and what you want it to look like when you're done. I'm confused about the layout that you showed in your follow up post. StumpedAgain wrote: I get a run-time error '424': Object required on the line: ActiveCell.Offset(2, 0).Cut Destination:=startspot.Offset(0, 2) in the following section. If I change the order and put the ActiveCell.Offset(2,0).cut line down a couple lines, I get the error on the ActiveCell.Offset(0,0).cut line leading me to believe that I'm somehow confusing the macro. Also, it works the first time through the loop. This error occurs the second time through the loop. Ahh!!! ActiveCell.Offset(1, 0).Cut Destination:=startspot.Offset(0, 1) ActiveCell.Offset(0, 0).Cut Destination:=startspot.Offset(0, 0) ActiveCell.Offset(2, 0).Cut Destination:=startspot.Offset(0, 2) 'error here startspot.Offset(1, 1).Value = Right(startspot.Offset(0, 1).Value, Len(startspot.Value) - 6) startspot.Offset(1, 0) = Right(startspot.Offset(0, 0).Value, Len(startspot.Value) - 6) startspot.Value = Left(startspot.Value, 4) startspot.Offset(0, 1).Value = Left(startspot.Offset(0, 1).Value, 4) startspot.Offset(1, 2).Value = Right(startspot.Offset(0, 2).Value, Len(startspot.Offset(0, 2).Value) - 7) startspot.Offset(0, 2).Value = Left(startspot.Offset(0, 2).Value, 5) -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Almost there with procedure error
Sure. Sorry for the confusion.
What I have: Date: 123 Name: 456 Shift: 789 Category A line 1 line 2 line 3 line 4 line 5 Category B line 1 line 2 line 3 line 4 line 5 line 6 Date: 123 Name: 456 Shift: 789 Category A line 1 line 2 line 3 line 4 line 5 Category B line 1 line 2 line 3 line 4 line 5 line 6 etc. What I want: Date Name Shift Category A Category B 123 456 789 line 1 line 1 line 2 line 2 line 3 line 3 line 4 line 4 line 5 line 5 line 6 Date Name Shift Category A Category B 345 678 123 line 1 line 1 line 2 line 2 etc. Hopefully it doesn't wrap it weird. Hope this helps! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Almost there with procedure error
You really have those "date:", "Name:", "Shift:" headers?
And empty cells between each of those groupings???? And those values in the cells are just plain old text--not formulas???? This just looks for the "Date:" in column A and then bases every group on that. It does expect exactly 2 categories per group. Option Explicit Sub testme() Dim CurWks As Worksheet Dim NewWks As Worksheet Dim FirstRow As Long Dim oRow As Long Dim BigRng As Range Dim SmallArea As Range Dim NextGroupMustBeFirstCategory As Boolean Dim RngToCopy As Range Dim oCol As Long Dim LinesPerGroup As Long Set CurWks = Worksheets("Sheet1") Set NewWks = Worksheets.Add With CurWks FirstRow = 1 Set BigRng = .Range(.Cells(FirstRow, "A"), _ .Cells(.Rows.Count, "A").End(xlUp)) _ .Cells.SpecialCells(xlCellTypeConstants) oRow = 0 LinesPerGroup = 1 For Each SmallArea In BigRng.Areas If LCase(SmallArea.Cells(1, 1).Value) Like LCase("Date:*") Then 'This is the Date/name/shift group 'Start of a new group. oRow = oRow + LinesPerGroup NewWks.Cells(oRow, "A").Resize(1, 5).Value _ = Array("Date", "Name", "Shift", "Category A", "Category B") oRow = oRow + 1 'remove "Date: " NewWks.Cells(oRow, "A").Value _ = Trim(Mid(SmallArea.Cells(1, 1).Value, 6)) 'remove "Name: " NewWks.Cells(oRow, "B").Value _ = Trim(Mid(SmallArea.Cells(2, 1).Value, 6)) 'remove "Shift: " NewWks.Cells(oRow, "C").Value _ = Trim(Mid(SmallArea.Cells(3, 1).Value, 7)) NextGroupMustBeFirstCategory = True LinesPerGroup = 3 Else 'This is the category A or Category B section. With SmallArea If .Cells.Count LinesPerGroup Then LinesPerGroup = .Cells.Count End If Set RngToCopy = .Resize(.Rows.Count - 1, 1).Offset(1, 0) End With If NextGroupMustBeFirstCategory Then oCol = 4 'column D 'get ready for the category B group NextGroupMustBeFirstCategory = False Else oCol = 5 'column E End If RngToCopy.Copy NewWks.Cells(oRow, oCol).PasteSpecial End If Next SmallArea End With Application.CutCopyMode = False NewWks.UsedRange.Columns.AutoFit End Sub StumpedAgain wrote: Sure. Sorry for the confusion. What I have: Date: 123 Name: 456 Shift: 789 Category A line 1 line 2 line 3 line 4 line 5 Category B line 1 line 2 line 3 line 4 line 5 line 6 Date: 123 Name: 456 Shift: 789 Category A line 1 line 2 line 3 line 4 line 5 Category B line 1 line 2 line 3 line 4 line 5 line 6 etc. What I want: Date Name Shift Category A Category B 123 456 789 line 1 line 1 line 2 line 2 line 3 line 3 line 4 line 4 line 5 line 5 line 6 Date Name Shift Category A Category B 345 678 123 line 1 line 1 line 2 line 2 etc. Hopefully it doesn't wrap it weird. Hope this helps! -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Almost there with procedure error
You are the man. Still don't know what was wrong with mine but meh... yours
works just fine! Thanks!!! "Dave Peterson" wrote: You really have those "date:", "Name:", "Shift:" headers? And empty cells between each of those groupings???? And those values in the cells are just plain old text--not formulas???? This just looks for the "Date:" in column A and then bases every group on that. It does expect exactly 2 categories per group. Option Explicit Sub testme() Dim CurWks As Worksheet Dim NewWks As Worksheet Dim FirstRow As Long Dim oRow As Long Dim BigRng As Range Dim SmallArea As Range Dim NextGroupMustBeFirstCategory As Boolean Dim RngToCopy As Range Dim oCol As Long Dim LinesPerGroup As Long Set CurWks = Worksheets("Sheet1") Set NewWks = Worksheets.Add With CurWks FirstRow = 1 Set BigRng = .Range(.Cells(FirstRow, "A"), _ .Cells(.Rows.Count, "A").End(xlUp)) _ .Cells.SpecialCells(xlCellTypeConstants) oRow = 0 LinesPerGroup = 1 For Each SmallArea In BigRng.Areas If LCase(SmallArea.Cells(1, 1).Value) Like LCase("Date:*") Then 'This is the Date/name/shift group 'Start of a new group. oRow = oRow + LinesPerGroup NewWks.Cells(oRow, "A").Resize(1, 5).Value _ = Array("Date", "Name", "Shift", "Category A", "Category B") oRow = oRow + 1 'remove "Date: " NewWks.Cells(oRow, "A").Value _ = Trim(Mid(SmallArea.Cells(1, 1).Value, 6)) 'remove "Name: " NewWks.Cells(oRow, "B").Value _ = Trim(Mid(SmallArea.Cells(2, 1).Value, 6)) 'remove "Shift: " NewWks.Cells(oRow, "C").Value _ = Trim(Mid(SmallArea.Cells(3, 1).Value, 7)) NextGroupMustBeFirstCategory = True LinesPerGroup = 3 Else 'This is the category A or Category B section. With SmallArea If .Cells.Count LinesPerGroup Then LinesPerGroup = .Cells.Count End If Set RngToCopy = .Resize(.Rows.Count - 1, 1).Offset(1, 0) End With If NextGroupMustBeFirstCategory Then oCol = 4 'column D 'get ready for the category B group NextGroupMustBeFirstCategory = False Else oCol = 5 'column E End If RngToCopy.Copy NewWks.Cells(oRow, oCol).PasteSpecial End If Next SmallArea End With Application.CutCopyMode = False NewWks.UsedRange.Columns.AutoFit End Sub StumpedAgain wrote: Sure. Sorry for the confusion. What I have: Date: 123 Name: 456 Shift: 789 Category A line 1 line 2 line 3 line 4 line 5 Category B line 1 line 2 line 3 line 4 line 5 line 6 Date: 123 Name: 456 Shift: 789 Category A line 1 line 2 line 3 line 4 line 5 Category B line 1 line 2 line 3 line 4 line 5 line 6 etc. What I want: Date Name Shift Category A Category B 123 456 789 line 1 line 1 line 2 line 2 line 3 line 3 line 4 line 4 line 5 line 5 line 6 Date Name Shift Category A Category B 345 678 123 line 1 line 1 line 2 line 2 etc. Hopefully it doesn't wrap it weird. Hope this helps! -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Almost there with procedure error
Actually, I really wanted this line:
RngToCopy.Copy NewWks.Cells(oRow, oCol).PasteSpecial to be: RngToCopy.Copy _ destination:=NewWks.Cells(oRow, oCol) But in this case, there really isn't a difference. StumpedAgain wrote: You are the man. Still don't know what was wrong with mine but meh... yours works just fine! Thanks!!! "Dave Peterson" wrote: You really have those "date:", "Name:", "Shift:" headers? And empty cells between each of those groupings???? And those values in the cells are just plain old text--not formulas???? This just looks for the "Date:" in column A and then bases every group on that. It does expect exactly 2 categories per group. Option Explicit Sub testme() Dim CurWks As Worksheet Dim NewWks As Worksheet Dim FirstRow As Long Dim oRow As Long Dim BigRng As Range Dim SmallArea As Range Dim NextGroupMustBeFirstCategory As Boolean Dim RngToCopy As Range Dim oCol As Long Dim LinesPerGroup As Long Set CurWks = Worksheets("Sheet1") Set NewWks = Worksheets.Add With CurWks FirstRow = 1 Set BigRng = .Range(.Cells(FirstRow, "A"), _ .Cells(.Rows.Count, "A").End(xlUp)) _ .Cells.SpecialCells(xlCellTypeConstants) oRow = 0 LinesPerGroup = 1 For Each SmallArea In BigRng.Areas If LCase(SmallArea.Cells(1, 1).Value) Like LCase("Date:*") Then 'This is the Date/name/shift group 'Start of a new group. oRow = oRow + LinesPerGroup NewWks.Cells(oRow, "A").Resize(1, 5).Value _ = Array("Date", "Name", "Shift", "Category A", "Category B") oRow = oRow + 1 'remove "Date: " NewWks.Cells(oRow, "A").Value _ = Trim(Mid(SmallArea.Cells(1, 1).Value, 6)) 'remove "Name: " NewWks.Cells(oRow, "B").Value _ = Trim(Mid(SmallArea.Cells(2, 1).Value, 6)) 'remove "Shift: " NewWks.Cells(oRow, "C").Value _ = Trim(Mid(SmallArea.Cells(3, 1).Value, 7)) NextGroupMustBeFirstCategory = True LinesPerGroup = 3 Else 'This is the category A or Category B section. With SmallArea If .Cells.Count LinesPerGroup Then LinesPerGroup = .Cells.Count End If Set RngToCopy = .Resize(.Rows.Count - 1, 1).Offset(1, 0) End With If NextGroupMustBeFirstCategory Then oCol = 4 'column D 'get ready for the category B group NextGroupMustBeFirstCategory = False Else oCol = 5 'column E End If RngToCopy.Copy NewWks.Cells(oRow, oCol).PasteSpecial End If Next SmallArea End With Application.CutCopyMode = False NewWks.UsedRange.Columns.AutoFit End Sub StumpedAgain wrote: Sure. Sorry for the confusion. What I have: Date: 123 Name: 456 Shift: 789 Category A line 1 line 2 line 3 line 4 line 5 Category B line 1 line 2 line 3 line 4 line 5 line 6 Date: 123 Name: 456 Shift: 789 Category A line 1 line 2 line 3 line 4 line 5 Category B line 1 line 2 line 3 line 4 line 5 line 6 etc. What I want: Date Name Shift Category A Category B 123 456 789 line 1 line 1 line 2 line 2 line 3 line 3 line 4 line 4 line 5 line 5 line 6 Date Name Shift Category A Category B 345 678 123 line 1 line 1 line 2 line 2 etc. Hopefully it doesn't wrap it weird. Hope this helps! -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Procedure too large error | Excel Programming | |||
Form Err.Raise error not trapped by entry procedure error handler | Excel Programming | |||
VBA Compile error: Procedure too large? | Excel Discussion (Misc queries) | |||
Compile error: Procedure too large | Excel Programming | |||
Error: The procedure number is out of range | Excel Programming |