Do while VB Macro

sanj_shah

New Member
Joined
Apr 10, 2005
Messages
15
Hi,

I have a worksheet like below:

I have separated each column with a comma below for the layout in this post ie.
column1,column2,column3

aaa1,bb1,ccc1
, ,ccc2
, ,ccc3
, ,ccc4
, ,ccc5
,bb2,ccc1
, ,ccc2
, ,ccc3
, ,ccc4
,bb3,ccc1
, ,ccc2
, ,ccc3
aaa2,bb1,ccc1
, ,ccc2
, ,ccc3
, ,ccc4
, ,ccc5
,bb2,ccc1
, ,ccc2
, ,ccc3
, ,ccc4
aaa3,bb1,ccc1
, ,ccc2
, ,ccc3
, ,ccc4
, ,ccc5

etc

what I would like is to create the following value to be pasted into another
cell (i.e. A1000)

aaa1=["bb1","ccc1","ccc2","ccc3","ccc4","ccc5","bb2","ccc1","ccc2","ccc3","c
cc4","bb3","ccc1","ccc2","ccc3"]&aaa2=["bb1","ccc1","ccc2","ccc3","ccc4","cc
c5","bb2","ccc1","ccc2","ccc3","ccc4"]&aaa3=["bb1","ccc1","ccc2","ccc3","ccc
4","ccc5"];

I tried to use the do while loop but cannot get this to work, could someone
please point me in the right direction.

Thanks!

Sanj
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Give this a try:

Code:
Sub test()
Dim x As Long, tmp As String
For x = 1 To Range("C65536").End(xlUp).Row + 1
    If Range("A" & x) <> "" Then tmp = tmp & "&" & Range("A" & x) & "=["
    If Trim(Range("B" & x)) <> "" Then tmp = tmp & Chr(34) & Trim(Range("B" & x)) & Chr(34) & " "
    If Trim(Range("C" & x)) <> "" Then tmp = tmp & Chr(34) & Trim(Range("C" & x)) & Chr(34) & " "
    If Range("A" & x).Offset(1, 0) <> "" Then tmp = tmp & "]"
Next x
tmp = Replace(Replace(Application.Trim(tmp), " ", ", "), ", ]", "]") & "];"
Range("A1000") = tmp
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,118,456
Messages
5,572,241
Members
412,450
Latest member
Heba
Top