• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

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
0
mortar
Asked:
mortar
  • 3
  • 2
  • 2
  • +2
2 Solutions
 
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
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
gecko_au2003Commented:
I noticed you used If statements, what about select case instead ?? Make it simpler and be easier to understand :)
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
 
ColosseoCommented:
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now