vba arrays

[Danny54]

A	B	C	D	E	F	G	H
aaa2
aaa3			aaa2	aaa3	aaa4	aaa5
aaa4		bbb1				bbb5	bbb6
aaa5
bbb1
bbb4
bbb5
bbb6

<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>
</tbody>

I have data in column "A" and need it transposed to what's above in column Range C2-H3. (manually created)
I believe I need to use a array of some kind but can't wrap my head around how to get them into the array much less printed out.

Suggestions?

Thanks

 

[mohadin]

Hi,
what did you do with bbb4?
Any logic
Regards

 

[DanteAmor]

Assuming your information like this:


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >aaa2</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >aaa3</td><td > </td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; ">aaa2</td><td style="background-color:#d7e4bc; ">aaa3</td><td style="background-color:#d7e4bc; ">aaa4</td><td style="background-color:#d7e4bc; ">aaa5</td><td style="background-color:#d7e4bc; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >aaa4</td><td > </td><td style="background-color:#d7e4bc; ">bbb1</td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; ">bbb4</td><td style="background-color:#d7e4bc; ">bbb5</td><td style="background-color:#d7e4bc; ">bbb6</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >aaa5</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >bbb1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >bbb4</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >bbb5</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >bbb6</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table>

Try this macro:

Sub transposed_data()
  Dim c As Range, j As Long, r As Range, n As Variant
  Set r = Range("C2:H3")
  r.Cells.ClearContents
  j = 1
  For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
    If c.Value = "" Then
      j = j + 1
    Else
      n = Val(Right(c.Value, 1))
      r.Cells(j, n) = c
    End If
  Next
End Sub

 

[Danny54]

Thanks so much.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.