Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Question for Stephen - find replace code
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Question for Stephen - find replace code
Resolved · Urgent Priority · Version 2003
Reply Fri 5 Jun 2009, 09:35Delegate Scott said...
Scott has attended:
Access Introduction course
Access Intermediate course
Access Advanced course
Access VBA course
Question for Stephen - find replace code
I have this code to find "colon" minutes and convert it to decimal minutes. It works, but it is a bit clunky and can take in excess of 10 minutes to run. I think it is more to do with the selection than the conversion code. The cells have hours and minutes in them. Any help would be very helpful, I need to prove to the IT desk that any excessive processing time is caused by thier hardware and not my code!
regards
Scott
Option Explicit
Sub Main()
Dim Counter As Integer
Dim RowMax As Integer, ColMax As Integer
Dim r As Integer, c As Integer
Dim PctDone As Single
If TypeName(ActiveSheet) <> "Worksheet" Then
Unload userForm2
Exit Sub
End If
RowMax = 692
ColMax = 19
For r = 1 To ColMax
For c = 1 To RowMax
Range("D:D,F:F,H:H,K:K,N:N,P:P").Select
Range("P692").Activate
Selection.Replace What:=":01", Replacement:=".009", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":02", Replacement:=".026", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":03", Replacement:=".042", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":04", Replacement:=".059", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":05", Replacement:=".076", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":06", Replacement:=".093", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":07", Replacement:=".110", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":08", Replacement:=".126", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":09", Replacement:=".143", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":10", Replacement:=".160", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":11", Replacement:=".177", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":12", Replacement:=".193", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":13", Replacement:=".210", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":14", Replacement:=".227", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":15", Replacement:=".244", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":16", Replacement:=".261", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":17", Replacement:=".277", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":18", Replacement:=".294", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":19", Replacement:=".311", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":20", Replacement:=".328", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":21", Replacement:=".344", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":22", Replacement:=".361", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":23", Replacement:=".378", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":24", Replacement:=".395", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":25", Replacement:=".412", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":26", Replacement:=".428", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":27", Replacement:=".445", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":28", Replacement:=".462", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":29", Replacement:=".479", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":30", Replacement:=".495", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":31", Replacement:=".512", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":32", Replacement:=".529", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":33", Replacement:=".546", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":34", Replacement:=".563", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":35", Replacement:=".579", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":36", Replacement:=".596", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":37", Replacement:=".613", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":38", Replacement:=".630", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":39", Replacement:=".647", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":40", Replacement:=".663", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":41", Replacement:=".680", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":42", Replacement:=".697", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":43", Replacement:=".714", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":44", Replacement:=".730", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":45", Replacement:=".747", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":46", Replacement:=".764", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":47", Replacement:=".781", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":48", Replacement:=".798", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":49", Replacement:=".831", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":50", Replacement:=".831", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":51", Replacement:=".848", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":52", Replacement:=".865", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":53", Replacement:=".881", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":54", Replacement:=".898", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":55", Replacement:=".915", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":56", Replacement:=".932", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":57", Replacement:=".949", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":58", Replacement:=".965", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":59", Replacement:=".982", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=":00", Replacement:=".00", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.NumberFormat = "0.00"
Counter = Counter + 1
Next c
PctDone = Counter / (RowMax * ColMax)
Call UpdateProgress(PctDone)
Next r
Unload userForm2
End Sub
For upcoming training course dates see: Pricing & availability
RE: Question for Stephen - find replace code
Hi Scott
Thanks for your post, it is quite specific and I will need to ask one of my more experienced colleagues to review this, they won't be able to do this until Wednesday.
It is worth noting that this sort of request does fall outside of the scope of this forum, but we will do our best to review and propose any solutions we find. Should there be any work we can do to assist we will let know the scope of work and any related development time we will need.
Regards
Jacob
|