macro to split cell contents

[johngio]

Hi all,

I have a range of cells (AB14:AB50)with values in them such as:

Wed-11-Oct 06:29
Wed-11-Oct 12:09
Wed-11-Oct 13:46
Wed-11-Oct 18:27
Thu-12-Oct 04:37
Thu-12-Oct 07:37
Thu-12-Oct 14:30


These are values entered by the user.

I also have 2 columns on my spreadsheet that list the date (in the B column) and the hour of the day (in the C column).

I.e.
B col_______________C col
11/10/06____________6
___________________7
___________________8
___________________9
___________________10
___________________11
___________________12
___________________13
___________________14

What I would like to do is write a macro to take all the dates and times entered by the user, and paste them in the D column next to the corresponding Date and time.

B col_______________C col________D col
11/10/06____________6___________Wed-11-Oct 06:29
___________________7
___________________8
___________________9
___________________10
___________________11
___________________12____________Wed-11-Oct 12:09
___________________13
___________________14____________Wed-11-Oct 13:46

I was thinking if I could specify the range for the user entered dates and split them into a date and time component (rounded to the nearest hour), then for each value in the range look in the B column for the corresponding date, and then the corresponding time in the C column, and then enter a value in the D column using an offset.

Complicated I know.

Any ideas?

Currently reading the Excel 2003 Power Programming with VBA book to try work some of this out

Cheers

John

 

[agihcam]

is the range(Ab14:ab50) formatted as text or "ddd-dd-mmm hh:mm"?

 

[johngio]

As far as Excel is concerned this is formatted as text (as it is cut and pasted from another spreadsheet).

Cheers

 

[johngio]

If it makes it simpler I can quite easily separate the text into a date and time using a couple of columns of code manaully . . .