Hi,
I have a VBA code i would like to create.
I have two data sets based on Transport sales. One is Adverts with Open/Close times. The second is Sale Times.
Sheet 1:
[TABLE="width: 144"]
<TBODY>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]Time
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Open/Close
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Type
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]08:00
[/TD]
[TD="class: xl63, bgcolor: transparent"]O
[/TD]
[TD="class: xl63, bgcolor: transparent"]Cars
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]08:00
[/TD]
[TD="class: xl63, bgcolor: transparent"]O
[/TD]
[TD="class: xl63, bgcolor: transparent"]Trucks
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]08:00
[/TD]
[TD="class: xl63, bgcolor: transparent"]O
[/TD]
[TD="class: xl63, bgcolor: transparent"]Bikes
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]08:00
[/TD]
[TD="class: xl63, bgcolor: transparent"]O
[/TD]
[TD="class: xl63, bgcolor: transparent"]Shoes
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]09:30
[/TD]
[TD="class: xl63, bgcolor: transparent"]C
[/TD]
[TD="class: xl63, bgcolor: transparent"]Trucks
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]10:00
[/TD]
[TD="class: xl63, bgcolor: transparent"]C
[/TD]
[TD="class: xl63, bgcolor: transparent"]Bikes
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]10:00
[/TD]
[TD="class: xl63, bgcolor: transparent"]O
[/TD]
[TD="class: xl63, bgcolor: transparent"]Trucks
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]12:00
[/TD]
[TD="class: xl63, bgcolor: transparent"]C
[/TD]
[TD="class: xl63, bgcolor: transparent"]Cars
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]12:30
[/TD]
[TD="class: xl63, bgcolor: transparent"]C
[/TD]
[TD="class: xl63, bgcolor: transparent"]Trucks
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]13:30
[/TD]
[TD="class: xl63, bgcolor: transparent"]C
[/TD]
[TD="class: xl63, bgcolor: transparent"]Shoes
[/TD]
[/TR]
</TBODY>[/TABLE]
Sheet 2:
[TABLE="width: 203"]
<TBODY>[TR]
[TD="align: center"]Time</SPAN>[/TD]
[TD="align: center"]Type</SPAN>[/TD]
[TD="align: center"]O/C[/TD]
[/TR]
[TR]
[TD="align: center"]08:30</SPAN>[/TD]
[TD="align: center"]Cars</SPAN>[/TD]
[TD="align: center"]O[/TD]
[/TR]
[TR]
[TD="align: center"]09:00</SPAN>[/TD]
[TD="align: center"]Bikes</SPAN>[/TD]
[TD="align: center"]O[/TD]
[/TR]
[TR]
[TD="align: center"]09:00</SPAN>[/TD]
[TD="align: center"]Shoes</SPAN>[/TD]
[TD="align: center"]O[/TD]
[/TR]
[TR]
[TD="align: center"]09:45</SPAN>[/TD]
[TD="align: center"]Trucks</SPAN>[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]10:30</SPAN>[/TD]
[TD="align: center"]Bikes</SPAN>[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]13:00</SPAN>[/TD]
[TD="align: center"]Shoes</SPAN>[/TD]
[TD="align: center"]O[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]
I need a macro to look at each Sale (Sheet 2), and return the value in column B (Open/Close). For the Type of transport. Output required in BOLD.
So for example, Sheet2: 09:45 Trucks, the advert before was 09:30 Trucks C. Therefore the advert was Closed for the sale. Hence the returned value needs to read C.
Conversely Sheet 2: 13:00 Shoes, the advert before was 08:00 Shoes O. Therefore the advert was Open and value returned should be O.
Thank you for your help.
tom0x
I have a VBA code i would like to create.
I have two data sets based on Transport sales. One is Adverts with Open/Close times. The second is Sale Times.
Sheet 1:
[TABLE="width: 144"]
<TBODY>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]Time
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Open/Close
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Type
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]08:00
[/TD]
[TD="class: xl63, bgcolor: transparent"]O
[/TD]
[TD="class: xl63, bgcolor: transparent"]Cars
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]08:00
[/TD]
[TD="class: xl63, bgcolor: transparent"]O
[/TD]
[TD="class: xl63, bgcolor: transparent"]Trucks
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]08:00
[/TD]
[TD="class: xl63, bgcolor: transparent"]O
[/TD]
[TD="class: xl63, bgcolor: transparent"]Bikes
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]08:00
[/TD]
[TD="class: xl63, bgcolor: transparent"]O
[/TD]
[TD="class: xl63, bgcolor: transparent"]Shoes
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]09:30
[/TD]
[TD="class: xl63, bgcolor: transparent"]C
[/TD]
[TD="class: xl63, bgcolor: transparent"]Trucks
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]10:00
[/TD]
[TD="class: xl63, bgcolor: transparent"]C
[/TD]
[TD="class: xl63, bgcolor: transparent"]Bikes
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]10:00
[/TD]
[TD="class: xl63, bgcolor: transparent"]O
[/TD]
[TD="class: xl63, bgcolor: transparent"]Trucks
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]12:00
[/TD]
[TD="class: xl63, bgcolor: transparent"]C
[/TD]
[TD="class: xl63, bgcolor: transparent"]Cars
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]12:30
[/TD]
[TD="class: xl63, bgcolor: transparent"]C
[/TD]
[TD="class: xl63, bgcolor: transparent"]Trucks
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]13:30
[/TD]
[TD="class: xl63, bgcolor: transparent"]C
[/TD]
[TD="class: xl63, bgcolor: transparent"]Shoes
[/TD]
[/TR]
</TBODY>[/TABLE]
Sheet 2:
[TABLE="width: 203"]
<TBODY>[TR]
[TD="align: center"]Time</SPAN>[/TD]
[TD="align: center"]Type</SPAN>[/TD]
[TD="align: center"]O/C[/TD]
[/TR]
[TR]
[TD="align: center"]08:30</SPAN>[/TD]
[TD="align: center"]Cars</SPAN>[/TD]
[TD="align: center"]O[/TD]
[/TR]
[TR]
[TD="align: center"]09:00</SPAN>[/TD]
[TD="align: center"]Bikes</SPAN>[/TD]
[TD="align: center"]O[/TD]
[/TR]
[TR]
[TD="align: center"]09:00</SPAN>[/TD]
[TD="align: center"]Shoes</SPAN>[/TD]
[TD="align: center"]O[/TD]
[/TR]
[TR]
[TD="align: center"]09:45</SPAN>[/TD]
[TD="align: center"]Trucks</SPAN>[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]10:30</SPAN>[/TD]
[TD="align: center"]Bikes</SPAN>[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]13:00</SPAN>[/TD]
[TD="align: center"]Shoes</SPAN>[/TD]
[TD="align: center"]O[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]
I need a macro to look at each Sale (Sheet 2), and return the value in column B (Open/Close). For the Type of transport. Output required in BOLD.
So for example, Sheet2: 09:45 Trucks, the advert before was 09:30 Trucks C. Therefore the advert was Closed for the sale. Hence the returned value needs to read C.
Conversely Sheet 2: 13:00 Shoes, the advert before was 08:00 Shoes O. Therefore the advert was Open and value returned should be O.
Thank you for your help.
tom0x