How do I handle #Value in VBA?

Hi Everyone,

In VBA, I'm searching for a substring in a cell using worksheet function FIND.
If FIND does not find the substring it returns a #Value (instead of 0 which
is what I want)

I'm not sure how to handle this.

here's my code...
....
With ActiveWorkbook.Worksheets("Sheet1")
Do While Not IsEmpty(Cells(i, 1))
If (Application.WorksheetFunction.Find("xyz", Cells(i, 7))
<> 0) Then
Worksheets("Sheet2").Cells(j, 1) =
Worksheets("Sheet1").Cells(i, 1)
i = i + 1
j = j + 1
End If
Loop
End With

If "xyz" does not exist, the code crashes. =( instead of just continuing

Thanks As Always!!! Kurt

If you just need to know whether or not a string matches a simple pattern,
you can use the LIKE operator:
If Cells(i, 7) LIKE "*xyz*" then
Here i use the wildcard character '*' meaning 'any number of characters'
therefore the expression means "cell contains the substring xyz or XYZ"

From the online help, other wildcard chars:
? --> Any single character.
*--> Zero or more characters.
#--> Any single digit (0–9).
[charlist] --> Any single character in charlist.
[!charlist] --> Any single character not in charlist

--
Regards,
Sébastien
<http://www.ondemandanalysis.com>


"kurt" wrote:

> Hi Everyone,
>
> In VBA, I'm searching for a substring in a cell using worksheet function FIND.
> If FIND does not find the substring it returns a #Value (instead of 0 which
> is what I want)
>
> I'm not sure how to handle this.
>
> here's my code...
> ...
> With ActiveWorkbook.Worksheets("Sheet1")
> Do While Not IsEmpty(Cells(i, 1))
> If (Application.WorksheetFunction.Find("xyz", Cells(i, 7))
> <> 0) Then
> Worksheets("Sheet2").Cells(j, 1) =
> Worksheets("Sheet1").Cells(i, 1)
> i = i + 1
> j = j + 1
> End If
> Loop
> End With
>
> If "xyz" does not exist, the code crashes. =( instead of just continuing
>
> Thanks As Always!!! Kurt

Let me know how this goes:

i = 1
j = 1
Do While Not IsEmpty(Cells(i, 1))
If InStr(Cells(i, 7), "xyz") Then
Worksheets("Sheet2").Cells(j, 1) = Worksheets("Sheet1").Cells(i, 1)
i = i + 1
j = j + 1
End If
Loop


"sebastienm" wrote:

> If you just need to know whether or not a string matches a simple pattern,
> you can use the LIKE operator:
> If Cells(i, 7) LIKE "*xyz*" then
> Here i use the wildcard character '*' meaning 'any number of characters'
> therefore the expression means "cell contains the substring xyz or XYZ"
>
> From the online help, other wildcard chars:
> ? --> Any single character.
> *--> Zero or more characters.
> #--> Any single digit (0–9).
> [charlist] --> Any single character in charlist.
> [!charlist] --> Any single character not in charlist
>
> --
> Regards,
> Sébastien
> <http://www.ondemandanalysis.com>
>
>
> "kurt" wrote:
>
> > Hi Everyone,
> >
> > In VBA, I'm searching for a substring in a cell using worksheet function FIND.
> > If FIND does not find the substring it returns a #Value (instead of 0 which
> > is what I want)
> >
> > I'm not sure how to handle this.
> >
> > here's my code...
> > ...
> > With ActiveWorkbook.Worksheets("Sheet1")
> > Do While Not IsEmpty(Cells(i, 1))
> > If (Application.WorksheetFunction.Find("xyz", Cells(i, 7))
> > <> 0) Then
> > Worksheets("Sheet2").Cells(j, 1) =
> > Worksheets("Sheet1").Cells(i, 1)
> > i = i + 1
> > j = j + 1
> > End If
> > Loop
> > End With
> >
> > If "xyz" does not exist, the code crashes. =( instead of just continuing
> >
> > Thanks As Always!!! Kurt

this was the one!

thanks! =)

Kurt

"Access101" wrote:

> Let me know how this goes:
>
> i = 1
> j = 1
> Do While Not IsEmpty(Cells(i, 1))
> If InStr(Cells(i, 7), "xyz") Then
> Worksheets("Sheet2").Cells(j, 1) = Worksheets("Sheet1").Cells(i, 1)
> i = i + 1
> j = j + 1
> End If
> Loop
>
>
> "sebastienm" wrote:
>
> > If you just need to know whether or not a string matches a simple pattern,
> > you can use the LIKE operator:
> > If Cells(i, 7) LIKE "*xyz*" then
> > Here i use the wildcard character '*' meaning 'any number of characters'
> > therefore the expression means "cell contains the substring xyz or XYZ"
> >
> > From the online help, other wildcard chars:
> > ? --> Any single character.
> > *--> Zero or more characters.
> > #--> Any single digit (0–9).
> > [charlist] --> Any single character in charlist.
> > [!charlist] --> Any single character not in charlist
> >
> > --
> > Regards,
> > Sébastien
> > <http://www.ondemandanalysis.com>
> >
> >
> > "kurt" wrote:
> >
> > > Hi Everyone,
> > >
> > > In VBA, I'm searching for a substring in a cell using worksheet function FIND.
> > > If FIND does not find the substring it returns a #Value (instead of 0 which
> > > is what I want)
> > >
> > > I'm not sure how to handle this.
> > >
> > > here's my code...
> > > ...
> > > With ActiveWorkbook.Worksheets("Sheet1")
> > > Do While Not IsEmpty(Cells(i, 1))
> > > If (Application.WorksheetFunction.Find("xyz", Cells(i, 7))
> > > <> 0) Then
> > > Worksheets("Sheet2").Cells(j, 1) =
> > > Worksheets("Sheet1").Cells(i, 1)
> > > i = i + 1
> > > j = j + 1
> > > End If
> > > Loop
> > > End With
> > >
> > > If "xyz" does not exist, the code crashes. =( instead of just continuing
> > >
> > > Thanks As Always!!! Kurt