problem in VB codes
 

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,

problem in VB codes
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"peyman" wrote in message
...
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,

problem in VB codes
 

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

--
Regards
Roger Govier



"peyman" wrote in message
...
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,

problem in VB codes
 

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

"Roger Govier" wrote:

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

--
Regards
Roger Govier



"peyman" wrote in message
...
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,

problem in VB codes
 

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


--
Regards
Roger Govier



"peyman" wrote in message
...
THANX rOGER,
IT WORKS, BUT i WONDER WHY "FIND" DOESN'T WORK IN THE LOOP??!!!
ANY IDEA?

"Roger Govier" wrote:

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

--
Regards
Roger Govier



"peyman" wrote in message
...
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,

problem in VB codes
 

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

"Roger Govier" wrote:

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


--
Regards
Roger Govier



"peyman" wrote in message
...
THANX rOGER,
IT WORKS, BUT i WONDER WHY "FIND" DOESN'T WORK IN THE LOOP??!!!
ANY IDEA?

"Roger Govier" wrote:

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

--
Regards
Roger Govier



"peyman" wrote in message
...
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,