I have a range of data 4 col wide and 400 rows deep. I need to convert the
data into a single column to post the data into another application.
Currently the data looks like this:

200 450.00 test newposting
202 3187362.58 test newposting
205 106392.58 test newposting

And I need it to look like this:
200
450.00
test
newposting
202
3187362.58
test
newposting
205
106392.58
test
newposting

How can I accomplish this without creating a copy/paste nightmare.

try to use the following macro:

Sub divideee()
counter = 0
For Each cell In Selection
For i = 1 To 4
cell.Offset(3 * counter + i - 1, 6) = cell.Offset(0, i - 1)
Next i
counter = counter + 1
Next cell
End Sub

HIH

This should work. You can improve it by replacing wxCurr.Rows in the For
Each loop to a defined range.

Sub ChangeLayout()

Dim wsCurr As Worksheet
Dim wsNew As Worksheet
Dim rRow As Range
Dim lngCountRow As Long

Set wsCurr = ActiveWorkbook.Worksheets(1)
Set wsNew = ActiveWorkbook.Worksheets(2)

lngCountRow = 1 ' set to whatever row you begin in

For Each rRow In wsCurr.Rows
If rRow.EntireRow.SpecialCells(xlCellTypeConstants).C ount = 1 Then

rRow.EntireRow.SpecialCells(xlCellTypeConstants).C opy
wsNew.Range("A" & lngCountRow).PasteSpecial Transpose:=True
lngCountRow = lngCountRow +
rRow.EntireRow.SpecialCells(xlCellTypeConstants).C ount
End If
Next rRow

End Sub

"scott" wrote:

I have a range of data 4 col wide and 400 rows deep. I need to convert the
data into a single column to post the data into another application.
Currently the data looks like this:

200 450.00 test newposting
202 3187362.58 test newposting
205 106392.58 test newposting

And I need it to look like this:
200
450.00
test
newposting
202
3187362.58
test
newposting
205
106392.58
test
newposting

How can I accomplish this without creating a copy/paste nightmare.

1. select yr cells containing 200, 202...
2. run the macro

the result will be 6 columns to the right from selection

The macro works great except that I loose my decimal places on all whole
dollars. How do I keep the formatting in the amount column?

"Jarek Kujawa" wrote:

try to use the following macro:

Sub divideee()
counter = 0
For Each cell In Selection
For i = 1 To 4
cell.Offset(3 * counter + i - 1, 6) = cell.Offset(0, i - 1)
Next i
counter = counter + 1
Next cell
End Sub

HIH

Hi,

Because I'm useless at VBA, I often resort to formula-based solutions to
problems like this, even though they look quite clumsy.
Here is what I use, assuming data is in columns A to D, then in E1, enter

=INDIRECT(IF(MOD(ROW(),4)=1,"A",IF(MOD(ROW(),4)=2, "B",(IF(MOD(ROW(),4)=3,"C",
"D"))))&ROUNDUP(ROW()/4,0))

and copy down.

Dave

url:http://www.ureader.com/msg/104235613.aspx

select yr resultant column

Sub formattt()
For i = 2 To 200000 Step 4
Cells(i, 1).NumberFormat = "0.00"
Next i
End Sub

adjust 200000 (maximum row number) as needed

sorry

should be:

select yr resultant column

Sub formattt()
For i = 2 To 200000 Step 4
Cells(i, 6).NumberFormat = "0.00"
Next i
End Sub

if the result is still in column 6

Only the second column in the range is a numeric cell the rest are text.
Does that effect the output? This macro gave me a runtime error when put
into the sheet.

Run-Time error '1004'
Application-defined or object-defined error

How can I correct this error?

Thanks for your help.

"Jarek Kujawa" wrote:

sorry

should be:

select yr resultant column

Sub formattt()
For i = 2 To 200000 Step 4
Cells(i, 6).NumberFormat = "0.00"
Next i
End Sub

if the result is still in column 6