PDA

View Full Version : [SOLVED] A Looping Question



snoopies
06-21-2005, 07:19 AM
Dear all,

How can I do the followings... thanks a lot!

Column A:
A
B
C
D

Column B:
1
2
3
4

I want to combine values of col A & B,
i.e add a new column in Col D, pls see below format:

A1
A2
A3
A4
B1
B2
B3
B4
C1
C2
C3
C4
D1
D2
D3
D4

Hope this is clear.. Pls advise.:)

mvidas
06-21-2005, 07:28 AM
Hi Snoopies,

There are a number of ways you can do this, depending on how advanced you want to get. Take a look at the following 3 examples:


Sub snoopiesRanges() 'Loop through each cell in ranges then combine
Dim RNG1 As Range, RNG2 As Range, dRow As Long, CLL1 As Range, CLL2 As Range
Set RNG1 = Range("A1", Range("A65536").End(xlUp)) 'could just use Range("A1:A4")
Set RNG2 = Range("B1", Range("B65536").End(xlUp)) 'could just use Range("B1:B4")
dRow = 1
For Each CLL1 In RNG1.Cells
For Each CLL2 In RNG2.Cells
Range("D" & dRow) = CLL1.Text & CLL2.Text
dRow = dRow + 1
Next 'CLL2
Next 'CLL1
End Sub


Sub snoopiesArray1() 'transfer cell values to array before combining
Dim RNG1, RNG2, dRow As Long, CLL1, CLL2
RNG1 = Range("A1", Range("A65536").End(xlUp)).Value 'could just use Range("A1:A4").Value
RNG2 = Range("B1", Range("B65536").End(xlUp)).Value 'could just use Range("B1:B4").Value
dRow = 1
For Each CLL1 In RNG1
For Each CLL2 In RNG2
Range("D" & dRow) = CLL1 & CLL2
dRow = dRow + 1
Next 'CLL2
Next 'CLL1
End Sub


Sub snoopiesArray2() 'pre-set array values
Dim RNG1, RNG2, dRow As Long, CLL1, CLL2
RNG1 = Array("A", "B", "C", "D")
RNG2 = Array(1, 2, 3, 4)
dRow = 1
For Each CLL1 In RNG1
For Each CLL2 In RNG2
Range("D" & dRow) = CLL1 & CLL2
dRow = dRow + 1
Next 'CLL2
Next 'CLL1
End Sub

The snoopiesRanges loops through each cell in both ranges, the snoopiesArray1 sets the values of the two ranges to the array variables, and the snoopiesArray2 sets the array values in the code rather than depend on the cell values. Up to you which way you want to use!
Matt

snoopies
06-21-2005, 07:41 AM
Hi Matt,

Thank you so much for your prompt reply.
It works! I'll know how to do it in the next time :rotlaugh: