PDA

View Full Version : [SOLVED] VBA Stacking cells on top of each other



ShawnMichael
05-17-2014, 03:40 AM
Hi All !

P.S I posted the same question on mrexcel.com but no one answered for 2 weeks, so I am posting here.

I have two columns in Sheet 1, Column A contains employee names (from cell A2:A250). And Column B contains 3 account names (from B2:B4).



Employee Names
Accounts


John Smith
Salaries & Wages


Mike Little
House Rent Allowance


Bill Jellen
Transport Allowance


Sean Paul



I want to create a list in Column C, which will do this:

Results



Salaries & Wages


John Smith


Salaries & Wages


Mike Little


Salaries & Wages


Bill Jellen


Salaries & Wages


Sean Paul


House Rent Allowance


John Smith


House Rent Allowance


Mike Little


House Rent Allowance


Bill Jellen


House Rent Allowance


Sean Paul



.....

So all the accounts in column B will be stacked on the names in Column A, with each name one by one, as in the above table.

I would be thankful for your help.

Regards

ashleyuk1984
05-17-2014, 04:29 AM
This is how I'd do it. - Here you go... Now corrected.


Sub Ash()
Dim x As Integer
Dim y As Integer
Dim Name As String
Dim Account As String
Dim LastRowNames As Integer
Dim LastRowAccounts As Integer


LastRowNames = Range("A" & Rows.Count).End(xlUp).Row
LastRowAccounts = Range("B" & Rows.Count).End(xlUp).Row


For x = 1 To LastRowAccounts
Account = Range("B" & x).Value
If Range("C1").Value = "" Then
Range("C1").Value = Account
Else
Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = Account
End If
For y = 1 To LastRowNames
Name = Range("A" & y).Value
If Range("C1").Value = "" Then
Range("C1").Value = Name
Else
Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = Name
If y < LastRowNames Then Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = Account
End If
Next y
Next x


End Sub

ShawnMichael
05-17-2014, 05:52 AM
Hi Ashley !

Thank you very much for this. Its working great.

Many many thanks and God Bless You :) :hi:

ShawnMichael
05-17-2014, 05:55 AM
Hi Again !

Can there be some kind of switch whereby we can Write names first and accounts under them (I mean reverse of what the macro is doing).

I mean, something like choose function, if something is 1, then do like what the above code does. If 2, then reverse the order (Names first, then account)?

Thank you for your help. :)

ShawnMichael
05-17-2014, 06:00 AM
Sorry, its ok. I got it. Just change the list from A to B, and the code will work for the reverse order as well. Thank you for your help. May God Bless You and Give You the Desire of your heart. :)