Change contents of a cell with VBA

[systematic]

Hi everyone,

I'm a little stuck and desperate for advice! My brain has turned to mush so now I turn you all for help.

I have a reference number in a receipting system. eg jd1407051455

The number is made up of two letters (initals of person), date (ddmmyy) and 24 hour time (hhmm). Using VBA I have managed to split the number up so that each piece of information sits in it's own column. (ie Column A = name, B = date, C = time). This was done by copying, pasting and then deleting irrelevant characters.

I now need to use VBA to insert the seperators so that excel recognises these numbers as dates and times respectively.

eg. Instead of 140705 in a cell, I need 14/07/05 and instead of 1455 I need 14:55.

I am at a complete loss as to how this can work. Just to throw a spanner in the works - I need to somehow make it work on a complete range (up to 1,000 rows of data) not just single cells.

If anyone has any ideas...I will be eternally grateful!

Thank you

sd

[JE McGimpsey]

One way:

Public Sub ParseReceipt()
Dim rCell As Range
With Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
.TextToColumns _
Destination:=.Cells(1), _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 2), Array(2, 4), Array(8, 1))
.Offset(0, 1).Resize(,1).Numberformat = "dd/mm/yy"
With .Offset(0, 2).Resize(, 1)
.NumberFormat = "hh:mm"
For Each rCell In .Cells
With rCell
.Value = TimeSerial( _
Int(.Value / 100), .Value Mod 100, 0)
End With
Next rCell
End With
End With
End Sub


In article <[email protected]>,
systematic <[email protected]>
wrote:

> Hi everyone,
>
> I'm a little stuck and desperate for advice! My brain has turned to
> mush so now I turn you all for help.
>
> I have a reference number in a receipting system. eg jd1407051455
>
> The number is made up of two letters (initals of person), date (ddmmyy)
> and 24 hour time (hhmm). Using VBA I have managed to split the number up
> so that each piece of information sits in it's own column. (ie Column A
> = name, B = date, C = time). This was done by copying, pasting and then
> deleting irrelevant characters.
>
> I now need to use VBA to insert the seperators so that excel recognises
> these numbers as dates and times respectively.
>
> eg. Instead of 140705 in a cell, I need 14/07/05 and instead of 1455 I
> need 14:55.
>
> I am at a complete loss as to how this can work. Just to throw a
> spanner in the works - I need to somehow make it work on a complete
> range (up to 1,000 rows of data) not just single cells.
>
> If anyone has any ideas...I will be eternally grateful!
>
> Thank you
>
> sd

[systematic]

Thank you so much for this! It (almost) does everything I need.

It works perfectly on the time column, but with the date not quite.

What is will do is convert a date serial number to the correct date (eg if the number is 1 it will convert it to 1 Jan 1900). However the dates in the reference number are not serial numbers.

ie. 140704 is 14/07/04.

Any ideas how to work around this?

Thanks again.

sd