macro to split cell contents

johngio

Board Regular
Joined
Jan 28, 2005
Messages
174
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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
is the range(Ab14:ab50) formatted as text or "ddd-dd-mmm hh:mm"?
 

johngio

Board Regular
Joined
Jan 28, 2005
Messages
174
As far as Excel is concerned this is formatted as text (as it is cut and pasted from another spreadsheet).

Cheers
 

johngio

Board Regular
Joined
Jan 28, 2005
Messages
174
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 . . .
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,003
Messages
5,856,779
Members
431,832
Latest member
i8urkeyboard

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
Top