Need help shortening run time

[Denny White]

:help

I have a portion of a subroutine that calculates times brought into the worksheet into 24hr time.

700 becomes 0700
1900 becomes 1900

The function adds 4 zeros to the front of the time then only prints the four numbers at the right.

0000700 becomes 0700
00001900 becomes 1900

This is taking over an hour to process.

Is there something I can do that has it skip the numbers that are already four digits?

This is the section doing the conversion.

'Convert IPS time and press time fields to military time
For count6 = 1 To RowsTailoredData

IPS_Time1 = "0000" & Cells(count6 + 1, 10)
Cells(count6 + 1, 10).Select
Selection.NumberFormat = "@"
Cells(count6 + 1, 10) = Right(IPS_Time1, 4)

Press_Time1 = "0000" & Cells(count6 + 1, 11)
Cells(count6 + 1, 11).Select
Selection.NumberFormat = "@"
Cells(count6 + 1, 11) = Right(Press_Time1, 4)

Next count6

All of this information is in about 1600 lines of imported data.

Then it is filtered down to only about 160 that are used to print forms used for a paper trail.

Can I place this so that it only is applied to the 160 lines rather than the 1600?

Your forum was a great help yesterday so I am hoping today as well.

: pray2: :bow: : pray2: :bow: : pray2:

[Bob Phillips]

Sub ConvertIPSTime()
'Convert IPS time and press time fields to military time

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For count6 = 1 To RowsTailoredData

With Cells(count6 + 1, 10)

IPS_Time1 = "0000" & .Value
.NumberFormat = "@"
.Value = Right(IPS_Time1, 4)
End With

With Cells(count6 + 1, 11)

Press_Time1 = "0000" & .Value
.NumberFormat = "@"
.Value = Right(Press_Time1, 4)
End With
Next count6

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

[Denny White]

Fall over fainted! !

That is the most incredible thing I have ever seen in my life ! !

I placed your code in replacement of what I had it took less than 1 minute to process.

Thank you so very much ! !

You guys rock ! ! ! :vv

[georgiboy]

Am i wrong in thinking that you could save more time by passing over values that are already four digits long with something like

If Len(count6.Value) <> 4 Then

'your code goes here

End If


correct me if i am wrong :)

[Denny White]

I have no idea !

I would think it would shorten it, in that usually more than half of the data is already four digits.

That would help even more.:dunno

[georgiboy]

To try

Sub ConvertIPSTime()
'Convert IPS time and press time fields to military time

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For count6 = 1 To RowsTailoredData

If Len(count6.Value) <> 4 Then

With Cells(count6 + 1, 10)

IPS_Time1 = "0000" & .Value
.NumberFormat = "@"
.Value = Right(IPS_Time1, 4)
End With

With Cells(count6 + 1, 11)

Press_Time1 = "0000" & .Value
.NumberFormat = "@"
.Value = Right(Press_Time1, 4)
End With

End If

Next count6

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

[Denny White]

:clap:

I haven't had a chance to try that one yet.

You guys are brilliant! ! ! !

:joy: :yay :joy: :yay

[GTO]

Greetings Denny (and georgiboy too),

Reference:


To try

'...
For count6 = 1 To RowsTailoredData

If Len(count6.Value) <> 4 Then

With Cells(count6 + 1, 10)

'...


'count6' is a number, from 1 to whatever RowsTailoredData was set to. Thus, .Value will cause an error.

Taking out the .Value results in testing how long (ie 1-9 = 1, 10-99 = 2, 100-999 = 3, etc) the numeric value of count6 is, so it is not testing what the cell value is at all.

You could test like:

For count6 = 1 To RowsTailoredData
If Len(Cells(count6 + 1, 10)) <> 4 Then '...

But...this means that you are only changing the format of the cells originally containing values less than 1000. Only an opinion, but I'd think you'd want all the cells formatted similarly.

As to the speed, in testing against a range of 3200 cells (two columns x 1600 rows):

No IF = 0.390625 seconds
W/ IF = 0.234375 seconds

While an IF can certainly speed up stuff, this is more when the "optional" or "not always needed" process is time-consuming; which it is not in this case.

Hope this helps,

Mark

[Bob Phillips]

If you need it even faster, this is about 3 times as fast as my original offereing



Sub ConvertIPSTime()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For count6 = 1 To RowsTailoredData

With Cells(count6 + 1, 10)

.Value = Right("0000" & .Value, 4)
End With

With Cells(count6 + 1, 11)

.Value = Right("0000" & .Value, 4)
End With

Next count6
Cells(count6 + 1, 10).Resize(RowsTailoredData - 1, 2).NumberFormat = "@"

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

[GTO]

@Bob:

Good morning Bob,

No matter what, wouldn't the cell formatting need to change before entering the data? I did test before opening my yapper here...; it would seem to me that .Value = Right("0000" & 245 <whatever three digit val) still hits the cell with the leading zero dropped.

Would moving the format change like below be efficient? (I am at home, and poor ol' "Wheezy" the laptop doesn't compare fairly)

I cannot skip mentioning however, that other than in drag racing or similar, well gosh, I've always thought of something occurring in .3 seconds (your first solution) as fairly quick:wot

Hope you're chuckling,

Mark

Option Explicit
Const RowsTailoredData As Long = 1601
Sub ConvertIPSTime()
Dim _
sngStart As Single, _
count6 As Long
sngStart = Timer

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Range(Cells(count6 + 1, 10), _
Cells(count6 + 1 + RowsTailoredData, 11)).NumberFormat = "@"

For count6 = 1 To RowsTailoredData

With Cells(count6 + 1, 10)

.Value = Right("0000" & .Value, 4)
End With

With Cells(count6 + 1, 11)

.Value = Right("0000" & .Value, 4)
End With

Next count6

' Cells(count6 + 1, 10).Resize(RowsTailoredData - 1, 2).NumberFormat = "@"

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

MsgBox Timer - sngStart
End Sub

[Denny White]

I cannot thank you all enough for all of the help.
:thumb :thumb :thumb :thumb :thumb :thumb

I have given credit to you all in my getting this worked out.
:cloud9: :cloud9: :cloud9: :cloud9: :cloud9: :cloud9:

I let numerous people in our company know of the very efficient assistance you have all been.

[Bob Phillips]

No matter what, wouldn't the cell formatting need to change before entering the data? I did test before opening my yapper here...; it would seem to me that .Value = Right("0000" & 245 <whatever three digit val) still hits the cell with the leading zero dropped.

Well no, it doesn't matter whether you apply a numberformat before or after.

But ... actually, there is no need to do it at all. We are creating a text string by taking the right-most 4 characters, and text doesn't haven't a numberformat, so we can drop it altogether.

[GTO]

Well no, it doesn't matter whether you apply a numberformat before or after.

But ... actually, there is no need to do it at all. We are creating a text string by taking the right-most 4 characters, and text doesn't haven't a numberformat, so we can drop it altogether.

Greetings Denny,

Well if your still checking this thread, you'll have to forgive me for a bit of levity.

We're thinking of writing a book, and were wondering whether you would object to "The Denny White Saga. Subtitled: How to get three or four responders to produce about a thousand lines of code (in about a year and a half) in regards to a rather simple request.


@Bob:

I believe you occasionally use the term "mate", for me, it will start:
My Brother, please don't become frustrated w/me, but I believe you have veered from the path that Denny was on. The object was to convert numbers such as 745 to 0745, ie: MIL time.

Though I didn't use the term numberformat, and am not wishing to misunderstand nomenclature, I was asking/suggesting that it would be necessary to format the cell as Text before assigning a val to it.

Now as you wrote at #9, you end up changing the format of the appropriate cells in rows (approx) 1601 to about 3201 to the Text format, and disclude the necessary changes to the actual appropriate cells. Further brother, it would matter as to whether the cells' formatting is changed before vs. after looping. I'll stop here, as this should be clear enough for all readers, and I'm sure you are already on the "same page" as me (and actually farther ahead of me...).

@Denny:

Now all the above said, as Bob is about 15 times smarter than I at VBA coding (and for all I know, better looking to boot), after reading Bob's last response, I was a bit hesitant in "correcting" his error straightaway. So... I ran this by a buddy, Scott (Demosthine), to ensure I wasn't just missing some hideously plain thing.

After answering in the affirmative that I was correct, Scott looked at me like I was a retard, and asked, why not...?

Sub Convert_ToMil()
Dim sStart As Single
sStart = Timer

Range(Cells(2, 10), Cells(RowsTailoredData, 11)).NumberFormat = "0000"
MsgBox "This procedure now only takes: " & Timer - sStart & " to run!" & vbCrLf & _
"It's too bad it took Mark, Bob, and Georgiboy like" & vbCrLf & _
"a thousand lines to write this - and Scott one to correct!!!", _
vbExclamation, ""

End Sub

So There!!! Now you have code that in testing, ran in .0078125 seconds:congaline and>>> :yay :yay :yay, and most importantly >>>:friends:

Hope this helped,

Mark

[GTO]

P.S. I forgot two things...

I believe we should all blame GeorgieBoy for everything after Post #04, as Bob's initial response was more than adequate.


And Denny, FINALLY, I meet someone in my own time zone!
:hi: and welcome to VBA Express!

Mark

[Bob Phillips]

Well, now you are pre-supposing that you know what the user wants/is trying to do. I have posted quite a lot of replies over the years, and the one thing I have learnt is that it is a waste of time trying to guess what they are doing it for, so I just tend to respond to the question.

If you want the fastest method of all, forget code, it takes time to write. Select the columns, Format>Cells,Custom, 0000

[GTO]

Greetings Bob,

Well shoot. While I well understand the pitfalls of the written word vs. verbal (what with the lack of facial/body expressions, tonal inflections, and all), I'm a little bummed that my humor (or attempt that is) fell so flat. I thought I'd leastwise get a chuckle.

Now while not overly anxious to appear thick-headed, I have read your last several times and am not sure. Again, while most of the verbiage was meant at humor, I am not seeing where I was pre-supposing. I used the OP's variable of RowsTailoredData, in case another section (below) in the columns is used for something. Are you talking about suggesting the custom number format by chance?

I agree with your take on overly guessing as to intent, thank you for that.

Hope you're having a nice Sunday,

Mark

[Bob Phillips]

You have just done exactly what you accused me of, failed to read the light-heartedness of my reply.

The pre-supposing I am referring to is in assuming that the OP doesn't want all of those zeroes in his data, formatting changes the look, not the content.

[GTO]

Well then I hope you will accept my apology. I did mis-take.

As to the custom number format, I guess I just felt "safe" with the goal of printing a paper trail stated. Regardless, your point is well taken in that this was still unnecessary presuming on my part.

Yackatchya later,

Mark