Changing cell and data format using vb

[drgogo]

Heres a puzzle

Im using the code shown below to convert a text number into a true excel time, i.e. 1345 into 13:45, well the code changes it to 1:45 pm and then i have to change to display format to show 13:45

Please Login or Register  to view this content.

Instead of doing this conversion in another column, is there a way via vb or a macro to perform this task on two columns of data with a custom menu button etc.

I want to be able to import my data from a csv file and then click a menu option and have two columns convert to proper time i.e from 1335 to 13:35

Hope that makes sense

G

[Domski]

This will convert 4 digit text strings to their serial time value:

Please Login or Register  to view this content.

Dom

[drgogo]

Thanks for that DOM

But looking at it it looks like the user would have to select the column and then convert the data, is there a way of doing it with out selecting the data, as the columns are the same on the csv sheets as i import them..

G

[Domski]

Like this:

Please Login or Register  to view this content.

Change A to the column you want it to work on.

Dom

[drgogo]

Sorry fro also sounding a bit stupid, but not that great with VBA in excel, where would i put that code..??

Just read your last post, would i need seprate code for each column or could this one bit of code cover a range, as the two columns that need converting are next to each other


G

[Domski]

Which columns would you like it to work on?

Easiest place to keep it would be in your personal macro workbook and then it's always available to you. Have a look here how to create one.

Dom

[Domski]

I'm off home now.

For column A:B the code would be:

Please Login or Register  to view this content.

To amend for D:E change this:

Please Login or Register  to view this content.

Hopefully you can amend to suit your needs.

Dom

[drgogo]

HIya DOM, just ran your code, kinder worked ok, except it did understand .numberformat

Also it has affected other cells on my spreadsheet and formatted other things

[Domski]

I'd probably need to see a sample of your workbook to see what's going on.

Dom