problem in VB codes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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,
 
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
 
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
 
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
 
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
 
Back
Top