problem in VB codes

P

peyman

hi,
can anybody tell me why when I put a loop for the following code, it doesn't
work!there's an error like:"unable to get the find property of the
worksheetfunction class"????!!!!!

Private Sub CommandButton4_Click()
Dim VAR As Variant
'For I = 13 To 32
VAR = Application.WorksheetFunction.Find("/164", Cells(13, 2))
If VAR >= 1 Then
Cells(13, 2) = Application.WorksheetFunction.Substitute(Cells(13, 2),
"/164", "")
End If
End Sub

the above codes work but the loop below NO?

Private Sub CommandButton4_Click()
Dim VAR As Variant
For I = 13 To 32
VAR = Application.WorksheetFunction.Find("/164", Cells(I, 2))
If VAR >= 1 Then
Cells(I, 2) = Application.WorksheetFunction.Substitute(Cells(I, 2), "/164",
"")
Else
End If
Next
End Sub
thanx,
 
D

Don Guillett

I tested your code with 2/164 and it left me with 2. This does the same. Is
that what you want?

Sub removefraction()
Range("b13:b32").Replace "/164", ""
End Sub
 
R

Roger Govier

Hi

You don't need the calculation of VAR.
Just substitute the values. If they are there, they will be substituted, if
not the values will be left as they are.

Private Sub CommandButton4_Click()
Dim I As Long
For I = 13 To 32
Cells(I, 2) = Application.WorksheetFunction.Substitute(Cells(I, 2), "/164",
"")
Next
End Sub
 
P

peyman

THANX rOGER,
IT WORKS, BUT i WONDER WHY "FIND" DOESN'T WORK IN THE LOOP??!!!
ANY IDEA?
 
R

Roger Govier

Hi

Its not a question of the loop, it only worked for you with a fixed value of
cells(13,2) because that cell did contain "1/164" - otherwise the same error
would have been shown if it didn't exist.

Did you see Don's posting? That is far more efficient with no looping at
all.
In case you missed it, i repeat it below

Sub removefraction()
Range("b13:b32").Replace "/164", ""
End Sub
 
P

peyman

thank you Roger for your explanation, but I like to know how the loop can be
practical for the codes, I mean "find" property.??
thanx
 
Top