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

How do I handle #Value in VBA?
 

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

How do I handle #Value in VBA?
 

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

How do I handle #Value in VBA?
 

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