PDA

View Full Version : [SOLVED] Need help with restructuring Macro Columns to rows, Please!!!



estatefinds
07-14-2017, 06:36 AM
I have data that is in column C that is numeric with dashes. I need the macro Columns to rows to be able to take this data that is found in the cell and move it to the Row.
Currently the macro on works with just numeric data, but not numeric data with "-" in between the numerica data.
So I need the macro to be able to do what it suppossed to as it does now but be able to work with the data in column C you ll find on the excell sheet that I attach.

Thank you very much!!! in advance

Sincerely Dennis



Sub movetocolumns()Dim i As Integer, iRow As Integer
Dim arrSource As Variant


'Set the first row
iRow = 1


With ActiveWorkbook.Worksheets("Sheet1")
'get the data into an array from the first column
arrSource = Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))


'parse every value of the array and add the data to the next column
For i = 1 To (UBound(arrSource) - UBound(arrSource) Mod 3) Step 3
.Cells(iRow, 2) = arrSource(i, 1)
.Cells(iRow, 3) = arrSource(i + 1, 1)
.Cells(iRow, 4) = arrSource(i + 2, 1)
iRow = iRow + 1
Next i
'add the remaining values
Select Case UBound(arrSource) Mod 3
Case 1 'one item to add
.Cells(iRow, 2) = arrSource(i, 1)
Case 2 'still two items to add
.Cells(iRow, 2) = arrSource(i, 1)
.Cells(iRow, 3) = arrSource(i + 1, 1)
Case Else 'nothing to add
End Select
End With
End Sub

mdmackillop
07-14-2017, 06:43 AM
Can you not do this with Data/Text to columns?

estatefinds
07-14-2017, 06:46 AM
I Prefer the macro to do this as it does work great and fast, please
Thank you:)

mdmackillop
07-14-2017, 06:54 AM
Sub Test()
Range("C1").CurrentRegion.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, OtherChar:="-"
End Sub

estatefinds
07-14-2017, 07:21 AM
i ran what you had posted and nothing happends?

mdmackillop
07-14-2017, 07:49 AM
Try the unedited version

Sub Macro4()
Range("C1").CurrentRegion.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1)), TrailingMinusNumbers:=True
End Sub

Paul_Hossler
07-14-2017, 08:15 AM
One problem is that Column A has no data, so the array is empty



arrSource = Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))


Another is that it is not very clear what you'd like the result to be formatted as

Please add an 'After' worksheet to your attachment

mdmackillop
07-14-2017, 08:18 AM
Hi Paul
He asks for the code to be applied to Column C

estatefinds
07-14-2017, 08:19 AM
that unmerges,
All i need is the data in the column C moved to rows. In post 3 the example shows what it looks like disregrd the hight lighted red.
so the data needs to be moved from column to rows with out altering the data that is in column C

mdmackillop
07-14-2017, 08:41 AM
Just change the destination to E1. I can't follow what the Post 3 example is intended to show; there seems no relation between column C and thae other data.

estatefinds
07-14-2017, 08:45 AM
sorry i forgot to add that data to worksheet, here it is Thankyou

estatefinds
07-14-2017, 09:02 AM
ok im sending file of what is suppossed to look like. the data will be moved from column C to rows starting a E1 to AL1 then resume placing data to E2 to AL2 and continue until all data is placed.


the data in column C is in order from top to bottom, so the data will be placed in rows starting at E1.

estatefinds
07-14-2017, 09:21 AM
review post 12 and attachment. thank you

mdmackillop
07-14-2017, 09:31 AM
Sub Test()
Set r = Range("C1").CurrentRegion
rw = r.Rows.Count
Set t = Cells(1, 5).Resize(1 + rw \ 34, 34)
For i = 1 To r.Rows.Count
t.Cells(i) = r(i)
Next
End Sub

estatefinds
07-14-2017, 10:33 AM
it worked great thank you very much!!!!:)