Stuck macro

B

Buck Rabbit

I have a list of Business names with their Address locations directly in the cell below, i.e.

Business Name
Address
Business Name
Address
Business Name
Address
Business Name
Address
Business Name
Address

I have tried to make a macro that will move each Address from the cell below to the next cell directly to the right of the
appropriate Business Name, i.e.

Business Name | Address
<blank row>
Business Name | Address
<blank row>
Business Name | Address
<blank row>
Business Name | Address
<blank row>

Thus far any macro I have created seems to get itself into an endless loop within the area I tried to create it until the DeBugger
cuts in and tell's me there is an error (obviously).
What I need to know is how to get the macro to move on down the list. It just seems to want to make the changes over the exact
same cells I used to create it and go no further.
I hope you can relate to what I've been trying to describe to you, thanks for all assistance.

Cheers
Buck
 
G

Gord Dibben

Buck

Sub ColtoRows()
Dim Rng As Range
Dim i As Long
Dim j As Long
Dim nocols As Integer
Set Rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
nocols = InputBox("Enter Number of Columns Desired")
For i = 1 To Rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(i, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(Rng.Row, "A")).ClearContents
Exit Sub
End Sub


Gord Dibben MS Excel MVP




I have a list of Business names with their Address locations directly in the cell below, i.e.

Business Name
Address
Business Name
Address
Business Name
Address
Business Name
Address
Business Name
Address

I have tried to make a macro that will move each Address from the cell below to the next cell directly to the right of the
appropriate Business Name, i.e.

Business Name | Address
<blank row>
Business Name | Address
<blank row>
Business Name | Address
<blank row>
Business Name | Address
<blank row>

Thus far any macro I have created seems to get itself into an endless loop within the area I tried to create it until the DeBugger
cuts in and tell's me there is an error (obviously).
What I need to know is how to get the macro to move on down the list. It just seems to want to make the changes over the exact
same cells I used to create it and go no further.
I hope you can relate to what I've been trying to describe to you, thanks for all assistance.

Cheers
Buck
Click to expand...

Gord Dibben MS Excel MVP
 
B

Buck Rabbit

Thank you very much, worked perfectly :)

Buck

Gord Dibben said:
Buck

Sub ColtoRows()
Dim Rng As Range
Dim i As Long
Dim j As Long
Dim nocols As Integer
Set Rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
nocols = InputBox("Enter Number of Columns Desired")
For i = 1 To Rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(i, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(Rng.Row, "A")).ClearContents
Exit Sub
End Sub


Gord Dibben MS Excel MVP






Gord Dibben MS Excel MVP
Click to expand...
 
Top