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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

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

Watch MrExcel Video

Forum statistics

Threads
1,133,463
Messages
5,658,912
Members
418,476
Latest member
Tristram_ZX81

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