I need VBA Script Help.

[chatz86]

Hello.

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

OOGO1.O1.13	Size
		30	31	32	33	34	36	38	40	Total
	32	2	3	3	0	4	3	1	0	16
	34	0	1	2	1	1	2	1	1	9
	Total	2	4	5	1	5	5	2	1	25

<colgroup><col><col><col><col><col span="2"><col><col><col span="4"></colgroup><tbody>
</tbody>

The above i want it to be converted into:

Complete code	Quantity
OOGO1.O1.13.30.32	2
OOGO1.O1.13.30.34	0
OOGO1.O1.13.31.32	3
OOGO1.O1.13.31.34	1
And so on…

<colgroup><col><col></colgroup><tbody>
</tbody>

Thank you so much in advance.

 

[venkat1926]

Sheet1

*	A	B	C	D	E	F	G	H	I	J
1	OOGO1.O1.13	Size	*	*	*	*	*	*	*	*
2	*	30	31	32	33	34	36	38	40	Total
3	32	2	3	3	0	4	3	1	0	16
4	34	0	1	2	1	1	2	1	1	9
5	Total	2	4	5	1	5	5	2	1	25

<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>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


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

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

 

[tusharm]

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).

Hello.

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

OOGO1.O1.13	Size
		30	31	32	33	34	36	38	40	Total
	32	2	3	3	0	4	3	1	0	16
	34	0	1	2	1	1	2	1	1	9
	Total	2	4	5	1	5	5	2	1	25

<colgroup><col><col><col><col><col span="2"><col><col><col span="4"></colgroup><tbody>
</tbody>

The above i want it to be converted into:

Complete code	Quantity
OOGO1.O1.13.30.32	2
OOGO1.O1.13.30.34	0
OOGO1.O1.13.31.32	3
OOGO1.O1.13.31.34	1
And so on…

<colgroup><col><col></colgroup><tbody>
</tbody>

Thank you so much in advance.

 

[mirabeau]

I guess you don't really need anything, , except maybe whatever's most convenient for the job in hand

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

 

[chatz86]

Guy i really appreciate all the code you put but i tried them all but im not getting the result i want. i will post the sample excel file. Download orderlist.xls from Sendspace.com - send big files the easy way thank you so much...