VBA to transpose dataset and add a piece of data

[wholly]

Need to write vba code to sort of transpose from a horizontal data set to a vertical data set with an extra piece of data created.

If the table 1 has no data for mon -> sun just skip

Is there an easy way.. I just seem to be way our of my depth here!!

This data set has like 10,000 centres, so doing it by hand will take forever!!!

Thanks in advance



TABLE 1 - Original

ServiceApprovalNumber	RatingsIssued	Last Service Approval Transfer Date	Annual Monday Start Time	Annual Monday End Time	Annual Tuesday Start Time	Annual Tuesday End Time	Annual Wednesday Start Time	Annual Wednesday End Time	Annual Thursday Start Time	Annual Thursday End Time	Annual Friday Start Time	Annual Friday End Time	listing_id
SE-00009863	Jul-16	########	6:30	18:30	6:30	18:30	6:30	18:30	6:30	18:30	6:30	18:30	155
SE-00009865	Jun-14	########	7.45	17.45	7.45	17.45	7.45	17.45	7.45	17.45	7.45	17.45	255
New Sheet - TABLE 2
ServiceApprovalNumber	listing_id	day	open_time	close_time		where day
SE-00009863	155	0	6:30	18:30			0 = Monday
SE-00009864	155	1	6:30	18:30			1 = Tuesday
SE-00009865	155	2	6:30	18:30			2 = Wednesday
SE-00009866	155	3	6:30	18:30
SE-00009867	155	4	6:30	18:30			skip if no hours data
SE-00009865	255	0	7.45	17.45
SE-00009866	255	1	7.45	17.45
SE-00009867	255	2	7.45	17.45
SE-00009868	255	3	7.45	17.45
SE-00009869	255	4	7.45	17.45

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

 

[wholly]

error

arr2(i, 1) = <Subscript out of range>



wayne

 

[wholly]

arr2(i, 1) = <Subscript out of range>

 

[wholly]

arr2(i, 1) = subscript out of range

< > is hiding the comment!!

 

[tonyyy]

BTW.. I am using your 1st script as all the numbers within the code are time formatted.

Please use the code in Post #3 .

Please re-state what you're trying to say in post #'s 11,12 and 13. It's not at all clear to me.

 

[wholly]

sorry for the delay Tonyyy

1. Updated script to Post#3

Error message is Runtime Error 9, subscript out of range

Posted pics below

Images not showing??? dont know why!

links are www.sweetsorella.com/excel1.jpg
www.sweetsorella.com/excel2.jpg

arr2(i, 1) = subscript out of range

thanks wayne

 

[tonyyy]

Try replacing this line...

arr1 = Sheets(1).UsedRange

with...

arr1 = Sheets("Sheet1").UsedRange.Value

And as before, replace "Sheet1" with your own worksheet name.

And if you want a little better performance from the code, add the following line just below the Dim statements...

Application.ScreenUpdating = False

 

[wholly]

Tonyyy

You bloody legend!!!!

Thank YOU!!!!

It works perfectly

Thanks you for your continual updates in helping me...

wayne

 

[tonyyy]

You're very welcome, Wayne...