Macro : Making label for each 3 column then border completely

muhammad susanto · Sep 23, 2015

hi all..

I need excel macro to figure out this below sample :

seq number	name	address	city
1	A KARIM	JL.HANG LEKIU NO 29 SUKAMULIA SAIL	PEKANBARU
2	A.MUIS J.	JL.SAOMATI RT 03/07, SAIL/TENAYAN RAYA	PEKANBARU
3	ABD. HARIS N	JL.BELANAK IV/452 LIMBUNGAN RUMBAI PESISIR	PEKANBARU
4	ABDUL AZIZ	JL.SAOMATI/TENAYAN RAYA/PEKANBARU	PEKANBARU
5	ABDUL HALIM	JL. KIJANG NO 24 HARJOSARI/SUKAJADI	PEKANBARU
6	ABDUL HARIS	JL.HANG TUAH KP. KELAPA NO 38 SAIL	PEKANBARU
7	ABDUL JALIL	JL. KEMUNING GG MESJID NO 30A SENAPELAN	PEKANBARU
8	ABDUL MUTHALIB	JL.GRIYA TIKA UTAMA SAI MINTAN BLOK N. NO.15 BKT.RAYA	PEKANBARU
9	ABDULFIKRI	JL.JEND.SUDIRMAN. ATAS SIMP.TIGA/B. RAYA	PEKANBARU
10	ABU BAKAR	JL. PESISIR NO 127 MERANTI PANDAK RUMBAI PESISIR	PEKANBARU
11	ACHMAD ZAILANI	JL.MUSLIMIN TANAH DATAR/PKB KOTA	PEKANBARU
12	ACHYAR YUDO	JL.PINUS GG.SUNGKAI NO 1 TANGKERANG UTARA	PEKANBARU
13	ADE FIRMANSYAH	JL. LEMBAH MULIA NO.01 SUKAMAJU/SAIL	PEKANBARU
14	ADI KASDI	JL. DURIAN NO 93 PEKANBARU	PEKANBARU
15	ADNAN	JL.KARTIKA UJUNG UMBAN SARI RUMBAI	PEKANBARU
16	ADRI.ST.SATI	JL.TASKURUN NO.34 WONOREJO/MARPOYAN DAMAI	PEKANBARU
17	ADRIAN KURNIAWAN	JL. LANGSAT NO.11	PEKANBARU
18	ADRIANI, DRA.	JL.RAWAWIRI NO.9 TKG.LABUAI	PEKANBARU
19	AFDA ARI	JL.KAPAS NO 4 BUKIT RAYA	PEKANBARU
20	AFLIHAN TELOS	JL. KEMBANG SARI NO 18 SAIL	PEKANBARU
21	AFNIATI	JL. HR SUBRANTAS RT.05/04 SIDOMULYO BARAT TAMPAN	PEKANBARU
22	AFRIELI	JL.HIJRAH Gg. BAKTI 15 A TANJUNG RHU LIMA PULUH	PEKANBARU
23	AGUS JALI	JL. ABDUL MUIS NO.32 CINTARAJA /SAIL	PEKANBARU
24	AGUSTAMI	JL SUKA KARYA GRAHA PANAM PERMAI F11 TUAH KARYA	PEKANBARU
25	AGUSTIN NUGRAHA	JL. MERAK SAKTI NO.11 SIMP. BARU/TAMPAN	PEKANBARU
26	AGUSWAR	JL.A.YANI GG TERENDAM III NO 124/PULAU KARAM	PEKANBARU
27	AGUSWIDARTI	JL.MERAK NO 15 KAMPUNG MELAYU/SUKAJADI	PEKANBARU
28	AHMAD GUNAWAN	JL.SUDIRMAN GG ASIA NO.11 TANAH DATAR	PEKANBARU
29	AHMAD THOHA	JL.GELUGUR UJUNG NO.56 TKG.UTARA	PEKANBARU
30	AIZAR	JL.PANDAN GG.CENDANA NO.8G, TKG.UTARA	PEKANBARU

<table width="1" border="1" bordercolor="#C0C0C0" bordercolordark="#FFFFFF" cellspacing="1" cellpadding="2" align="center" frame="border" rules="all">
<tr align="left" valign="top">
<th bgcolor="#FFFFFF" valign="middle" style="white-space: nowrap">seq number</th>
<th colspan="2" bgcolor="#D6DCE4" align="center" valign="middle" style="white-space: nowrap">expected result/after macro</th>
<th bgcolor="#FFFFFF" valign="middle" style="white-space: nowrap">seq number</th>
</tr>
<tr align="left" valign="top">
<td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap">1</td>
<td bgcolor="#FFFF00" valign="bottom" style="white-space: nowrap">A KARIM</td>
<td bgcolor="#548235" valign="bottom" style="white-space: nowrap">ADNAN</td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap">15</td>
</tr>
<tr align="left" valign="top">
<td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFF00" valign="bottom" style="white-space: nowrap">JL.HANG LEKIU NO 29 SUKAMULIA SAIL</td>
<td bgcolor="#548235" valign="bottom" style="white-space: nowrap">JL.KARTIKA UJUNG UMBAN SARI RUMBAI</td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"></td>
</tr>
<tr align="left" valign="top">
<td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFF00" valign="bottom" style="white-space: nowrap">PEKANBARU</td>
<td bgcolor="#548235" valign="bottom" style="white-space: nowrap">PEKANBARU</td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"></td>
</tr>
<tr align="left" valign="top">
<td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"></td>
</tr>
<tr align="left" valign="top">
<td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap">2</td>
<td bgcolor="#00B0F0" valign="bottom" style="white-space: nowrap">A.MUIS J.</td>
<td bgcolor="#FFD966" valign="bottom" style="white-space: nowrap">ADRI.ST.SATI</td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap">16</td>
</tr>
<tr align="left" valign="top">
<td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#00B0F0" valign="bottom" style="white-space: nowrap">JL.SAOMATI RT 03/07, SAIL/TENAYAN RAYA</td>
<td bgcolor="#FFD966" valign="bottom" style="white-space: nowrap">JL.TASKURUN NO.34 WONOREJO/MARPOYAN DAMAI</td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"></td>
</tr>
<tr align="left" valign="top">
<td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#00B0F0" valign="bottom" style="white-space: nowrap">PEKANBARU</td>
<td bgcolor="#FFD966" valign="bottom" style="white-space: nowrap">PEKANBARU</td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"></td>
</tr>
<tr align="left" valign="top">
<td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"></td>
</tr>
<tr align="left" valign="top">
<td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap">3</td>
<td bgcolor="#F4B084" valign="bottom" style="white-space: nowrap">ABD. HARIS N</td>
<td bgcolor="#305496" valign="bottom" style="white-space: nowrap">ADRIAN KURNIAWAN</td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap">17</td>
</tr>
<tr align="left" valign="top">
<td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#F4B084" valign="bottom" style="white-space: nowrap">JL.BELANAK IV/452 LIMBUNGAN RUMBAI PESISIR</td>
<td bgcolor="#305496" valign="bottom" style="white-space: nowrap">JL. LANGSAT NO.11</td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"></td>
</tr>
<tr align="left" valign="top">
<td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#F4B084" valign="bottom" style="white-space: nowrap">PEKANBARU</td>
<td bgcolor="#305496" valign="bottom" style="white-space: nowrap">PEKANBARU</td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"></td>
</tr>
<tr align="left" valign="top">
<td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"></td>
</tr>
<tr align="left" valign="top">
<td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap">4</td>
<td bgcolor="#FFFFFF" valign="bottom" style="white-space: nowrap">etc..</td>
<td bgcolor="#FFFFFF" valign="bottom" style="white-space: nowrap">etc…</td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap">18</td>
</tr>
<tr align="left" valign="top">
<td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"></td>
</tr>
<tr align="left" valign="top">
<td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"></td>
</tr>
<tr align="left" valign="top">
<td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"></td>
</tr>
<tr align="left" valign="top">
<td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap">5</td>
<td bgcolor="#FFFFFF" valign="bottom" style="white-space: nowrap">etc..</td>
<td bgcolor="#FFFFFF" valign="bottom" style="white-space: nowrap">etc…</td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap">19</td>
</tr>
<tr align="left" valign="top">
<td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"></td>
</tr>
<tr align="left" valign="top">
<td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"></td>
</tr>
<tr align="left" valign="top">
<td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"></td>
</tr>
</table>

muhammad susanto · Sep 23, 2015

please, check my attachment

https://app.box.com/s/wuc100qkpfc25258xh37978tohgim76z

muhammad susanto · Sep 23, 2015

additional information :

note :
1) transpose data for each 3 column in 3 line/row;
2) max tranpose data is 14 rows, start transpose from seq.number 1 to 14 (for left column)
and start seq.number 15 to seq.number 28 (as in right column)
3) coloring shading just the clue to easy grouped;
4) after transpose, giving border line for each data

muhammad susanto · Sep 23, 2015

it works but not fully exactly that's i want
like my sample,i need adding border line for each data :

Code:

Sub test()Dim i As Long, j As Long, myrow As Long, mycol As Long
For i = 2 To Cells(Rows.Count, "B").End(xlUp).Row
  mycol = (i - 2) \ 14
  myrow = 4 * (i - 2) - 14 * 4 * mycol + 1
  For j = 1 To 3
    Cells(myrow + j, mycol + 6) = Cells(i, j + 1)
  Next j
Next i
End Sub

https://app.box.com/s/wuc100qkpfc25258xh37978tohgim76z

how do make it?

Macro : Making label for each 3 column then border completely

muhammad susanto

Well-known Member

Excel Facts

muhammad susanto

Well-known Member

muhammad susanto

Well-known Member

muhammad susanto

Well-known Member

Similar threads

Forum statistics

Share this page

Macro : Making label for each 3 column then border completely

muhammad susanto

Well-known Member

Excel Facts

muhammad susanto

Well-known Member

muhammad susanto

Well-known Member

muhammad susanto

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock