I need VBA Script Help.

C

chatz86

New Member
Joined
Feb 1, 2013
Messages
2
Hello.

i have my list of order and i want it convert it into a specific set of code.

[TABLE="class: grid, width: 384"]
<colgroup><col><col><col><col><col span="2"><col><col><col span="4"></colgroup><tbody>[TR]
[TD]OOGO1.O1.13[/TD]
[TD]Size[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD]32[/TD]
[TD]33[/TD]
[TD="align: right"]34[/TD]
[TD]36[/TD]
[TD]38[/TD]
[TD]40[/TD]
[TD]Total[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]32[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]34[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Total[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The above i want it to be converted into:

[TABLE="class: grid, width: 168"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Complete code[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]OOGO1.O1.13.30.32[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]OOGO1.O1.13.30.34[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]OOGO1.O1.13.31.32[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]OOGO1.O1.13.31.34[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]And so on…[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thank you so much in advance.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
Sheet1

*ABCDEFGHIJ
OOGO1.O1.13Size********
*Total
Total

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:125px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]40[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]16[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]25[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


the configuration of data should be like the above only
now try this macro

Code: 
Sub test()
Dim endrow As Integer, result As Range
Dim size1 As Range, c1 As Range, size2 As Range, c2 As Range
Set size1 = Range(Range("B2"), Range("B2").End(xlToRight))
Set size2 = Range(Range("a3"), Range("a3").End(xlDown).Offset(-1, 0))
Range(Range("A3").End(xlDown).Offset(1, 0), Cells(Rows.Count, "A")).EntireRow.Delete


endrow = Cells(Rows.Count, "A").End(xlUp).Row
Set result = Cells(endrow, "A").Offset(5, 0)
'MsgBox result.Address
result = "complete code"
result.Offset(0, 1) = "quantity"
For Each c1 In size1
For Each c2 In size2
Set result = Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
result = Range("A1") & "." & c1 & "." & c2
result.Offset(0, 1) = Intersect(Columns(c1.Column), Rows(c2.Row))
Next c2
Next c1


End Sub
 
Upvote 0
No, you don't. Need code that is. {grin}

Suppose your data are in D2:N6. Suppose you want the result in B8:E8 on down.

Then, enter the following formulas:
B8: =INDEX($F$3:$M$3,INT((ROW()-ROW($C$8))/2)+1)
C8: =INDEX($E$4:$E$5,MOD(ROW()-ROW($C$8),2)+1)
D8: =$D$2&"."&B8&"."&C8
E8: =INDEX($F$4:$M$5,MOD(ROW()-ROW($C$8),2)+1,INT((ROW()-ROW($C$8))/2)+1)

Copy B8:E8 as far down as necessary (go too far and you will get error results in the cells).


chatz86 said:
Hello.

i have my list of order and i want it convert it into a specific set of code.

[TABLE="class: grid, width: 384"]
<colgroup><col><col><col><col><col span="2"><col><col><col span="4"></colgroup><tbody>[TR]
[TD]OOGO1.O1.13[/TD]
[TD]Size[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD]32[/TD]
[TD]33[/TD]
[TD="align: right"]34[/TD]
[TD]36[/TD]
[TD]38[/TD]
[TD]40[/TD]
[TD]Total[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]32[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]34[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Total[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The above i want it to be converted into:

[TABLE="class: grid, width: 168"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Complete code[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]OOGO1.O1.13.30.32[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]OOGO1.O1.13.30.34[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]OOGO1.O1.13.31.32[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]OOGO1.O1.13.31.34[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]And so on…[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thank you so much in advance.
Click to expand...
 
Upvote 0
I guess you don't really need anything, ;), except maybe whatever's most convenient for the job in hand
Code: 
Sub arrange()
Dim a, arr, d&, i&, j&
a = Range("A1").CurrentRegion
d = 2
For j = 2 To UBound(a, 2)
    For i = 3 To UBound(a, 1) - 1
        d = d + 1
        arr = Array(a(1, 1), a(2, j), a(i, 1))
        Cells(d + 6, 1) = Join(arr, ".")
        Cells(d + 6, 2) = a(i, j)
    Next i
Next j
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
Formula Error: Error applying formula with VBA even though it works manually
Replies
3
Views
83
6StringJazzer
6StringJazzer
D
  • Solved
ROUNDUP and MIN with IF/AND
Replies
6
Views
191
dreid1011
dreid1011
R
  • Solved
Ignore blanks for a SUM
Replies
2
Views
108
Rex987456
R
P
  • Question Question
ListBox Click to textbox & delete row
Replies
0
Views
592
pure vito
P
M
  • Question Question
I need help with VBA/macro script to collect user input and fill cells
Replies
4
Views
461
mumps
mumps

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top