VBA - Please optimize ;)

For count = 2 To 7992
    If (Mid(Range("f" & count).FormulaR1C1, 10, 1) & Mid(Range("f" & count).FormulaR1C1, 16, 1)) <> "//" Then
        Range("f" & count).Font.Bold = True
    End If
        Dim char1 As String, char2 As String, char3 As String, char4 As String, char5 As String
        char1 = Mid((Range("F" & count).FormulaR1C1), 5, 1)
        char2 = Mid((Range("F" & count).FormulaR1C1), 6, 1)
        char3 = Mid((Range("F" & count).FormulaR1C1), 7, 1)
        char4 = Mid((Range("F" & count).FormulaR1C1), 8, 1)
        char5 = Mid((Range("F" & count).FormulaR1C1), 9, 1)
        If char1 & char2 & char3 & char4 & char5 = "     " Or Mid((Range("F" & count).FormulaR1C1), 6, 1) = "/" Then
        Else
            If char1 = "/" Then Range("f" & count).FormulaR1C1 = Left(Range("f" & count).FormulaR1C1, 4) & "     " & Mid(Range("f" & count).FormulaR1C1, 5, Len(Range("f" & count).FormulaR1C1) - 5)
            If char2 = "/" Then Range("f" & count).FormulaR1C1 = Left(Range("f" & count).FormulaR1C1, 4) & "    " & Mid(Range("f" & count).FormulaR1C1, 5, Len(Range("f" & count).FormulaR1C1) - 5)
            If char3 = "/" Then Range("f" & count).FormulaR1C1 = Left(Range("f" & count).FormulaR1C1, 4) & "   " & Mid(Range("f" & count).FormulaR1C1, 5, Len(Range("f" & count).FormulaR1C1) - 5)
            If char4 = "/" Then Range("f" & count).FormulaR1C1 = Left(Range("f" & count).FormulaR1C1, 4) & "  " & Mid(Range("f" & count).FormulaR1C1, 5, Len(Range("f" & count).FormulaR1C1) - 5)
            If char5 = "/" Then Range("f" & count).FormulaR1C1 = Left(Range("f" & count).FormulaR1C1, 4) & " " & Mid(Range("f" & count).FormulaR1C1, 5, Len(Range("f" & count).FormulaR1C1) - 5)
        End If
        If Mid((Range("f" & count).FormulaR1C1), 5, 6) = "      " Then Range("f" & count).FormulaR1C1 = Mid(Range("f" & count).FormulaR1C1, 1, 4) & Mid(Range("f" & count).FormulaR1C1, 6, Len(Range("f" & count).FormulaR1C1) - 5)
    Next count
LVL 4
mortarAsked:
Who is Participating?
 
gecko_au2003Connect With a Mentor Commented:
I noticed you used If statements, what about select case instead ?? Make it simpler and be easier to understand :)
0
 
Arthur_WoodCommented:
this block :

If char1 = "/" Then Range("f" & count).FormulaR1C1 = Left(Range("f" & count).FormulaR1C1, 4) & "     " & Mid(Range("f" & count).FormulaR1C1, 5, Len(Range("f" & count).FormulaR1C1) - 5)
            If char2 = "/" Then Range("f" & count).FormulaR1C1 = Left(Range("f" & count).FormulaR1C1, 4) & "    " & Mid(Range("f" & count).FormulaR1C1, 5, Len(Range("f" & count).FormulaR1C1) - 5)
            If char3 = "/" Then Range("f" & count).FormulaR1C1 = Left(Range("f" & count).FormulaR1C1, 4) & "   " & Mid(Range("f" & count).FormulaR1C1, 5, Len(Range("f" & count).FormulaR1C1) - 5)
            If char4 = "/" Then Range("f" & count).FormulaR1C1 = Left(Range("f" & count).FormulaR1C1, 4) & "  " & Mid(Range("f" & count).FormulaR1C1, 5, Len(Range("f" & count).FormulaR1C1) - 5)
            If char5 = "/" Then Range("f" & count).FormulaR1C1 = Left(Range("f" & count).FormulaR1C1, 4) & " " & Mid(Range("f" & count).FormulaR1C1, 5, Len(Range("f" & count).FormulaR1C1) - 5)


can be simplified to:

If char1 = "/"  or char2 = "/"  or char3 = "/"  or char3 = "/"  or char5 = "/"   Then
      Range("f" & count).FormulaR1C1 = Left(Range("f" & count).FormulaR1C1, 4) & "     " & Mid(Range("f" & count).FormulaR1C1, 5, Len(Range("f" & count).FormulaR1C1) - 5)
End If

AW
           
0
 
mortarAuthor Commented:
Does that allow char1 to have 5 spaces, char2 - 4 spaces, char3 - 3 spaces char4 - 2 spaces etc etc..

char2, char3, char4 and char5 are different..
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Arthur_WoodCommented:
ok, now I see what was different.  No, the suggestion does not allow that.  

This is very strange code.  What exactly are you doing with this, if I might ask.

AW
0
 
mortarAuthor Commented:
It is confidential so I can't go into specifics but the basic jist of it is I have to compare two sets of data, foreign data with local data.

The snip of code i've given you is from our data, and that snippet basically points out typing errors in a particular field. i.e. theres supposed to be 5 spaces in a particular point of this field, but because in the application used to handle their database information, a space here or there isn't easy to notice.  So if theres only one space, four are added, two spaces, three are added etc.

If I knew vb commands better like finding a paticular character out of a string or using the trim() command I think that'd work better..

Hope this helps.
0
 
mortarAuthor Commented:
Points increased to 200
0
 
Julian HansenCommented:
Can you not use the Format function to pad the string?

Format ( Str, "@@@@@\")

Where str is the cell or string or whatever
0
 
ColosseoConnect With a Mentor Commented:
Hi

this is my first pass on how i would write that piece of code. basically replacing char1 to char5 with an array allows the use of for loops to repeat the common code making it easier to read and maintain.

Unfortunately I dont have excel on this computer so I wont be able to test the code properly until tomorrow now but I thought I would post it so that you can at least have a look and maybe it will give you some ideas.

Cheers

Scott

Dim chars(5)
Dim strValue

For count = 2 To 7992

  strValue = Range("f" & count).FormulaR1C1

  If (Mid(strValue, 10, 1) & Mid(strValue, 16, 1)) <> "//" Then
    Range("f" & count).Font.Bold = True
  End If

  For i = 0 To 4
    chars(i) = Mid(strValue, i+5, 1)
  Next

  If Not Join(chars,"") = "     " And Not Mid(strValue, 6, 1) = "/" Then

    For i = 0 To 4
      If chars(i) = "/" Then
        Range("f" & count).FormulaR1C1 = Left(strValue, 4) & Left("     ",5-i) & Mid(strValue, 5, Len(strValue) - 5)
      End If
    Next

  End If

  If Mid(strValue, 5, 6) = "      " Then Range("f" & count).FormulaR1C1 = Mid(strValue, 1, 4) & Mid(strValue, 6, Len(strValue) - 5)
Next count
0
 
Julian HansenCommented:
Just as a matter of interest did the

Format ( Str, "@@@@@\")

Not work?

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.