Solved

# VBA - Please optimize ;)

Posted on 2005-05-04
239 Views
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
Question by:mortar

LVL 44

Expert Comment

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

LVL 4

Author Comment

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

LVL 44

Expert Comment

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

LVL 4

Author Comment

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

LVL 4

Author Comment

Points increased to 200
0

LVL 23

Accepted Solution

I noticed you used If statements, what about select case instead ?? Make it simpler and be easier to understand :)
0

LVL 49

Expert Comment

Can you not use the Format function to pad the string?

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

Where str is the cell or string or whatever
0

LVL 15

Assisted Solution

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

LVL 49

Expert Comment

Just as a matter of interest did the

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

Not work?

0

## Featured Post

### Suggested Solutions

sum67 challenge 35 76
withoutTen challenge 14 68
thread-safe code in c++ 2 58
pairs challenge 5 29
Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …