Using Array variable in FIND function
You have to define SearchIn as an array, not a range. Also, SearchIn(n) will
not work as n is not defined. I think the structure you're looking for might
look something like this. I assume you have a loop set up that increments
the variable "i". If you are looping through a range and want every cell
that contains the values you're looking for, you could use the Union function
instead of Exit For (in the Else statement).
Dim SearchIn(1 To 2) As String
Dim Rngia As Range
Dim n As Long
SearchIn(1) = "*employment *government"
SearchIn(2) = "*employment *manufacturing"
For n = LBound(SearchIn) To UBound(SearchIn)
If Rngia Is Nothing Then
Set Rngia = Cells(i, 1).Find(What:=SearchIn(n), _
LookIn:=xlValues, Lookat:=xlPart)
Else: Exit For
End If
Next n
With the Union function
Dim SearchIn(1 To 2) As String
Dim Rngia As Range
Dim n As Long
Dim i As Long
On Error Resume Next
SearchIn(1) = "*employment *government"
SearchIn(2) = "*employment *manufacturing"
For i = 1 To 10
For n = LBound(SearchIn) To UBound(SearchIn)
If Rngia Is Nothing Then
Set Rngia = Cells(i, 1).Find(What:=SearchIn(n), _
LookIn:=xlValues, Lookat:=xlPart)
Else: Set Rngia = Union(Rngia, Cells(i, 1).Find(What:=SearchIn(n), _
LookIn:=xlValues, Lookat:=xlPart))
End If
Next n
Next i
Rngia.Select
"GreenInIowa" wrote:
I am using the following code to FIND "employment " and "government" and the
code is working fine.
Set rngia = Cells(i, 1).Find(What:="*employment *government",
LookIn:=xlValues, Lookat:=xlPart)
But I have others searches to do and thus, would like to use an array
variable for "What:=". Is this possible?
I tried to define "SearchIn" as array variable and used in FIND function,
but it is giving me error. I was wondering why?
Dim SearchIn As Range
SearchIn(1) = "*employment *government"
SearchIn(2) = "*employment *manufacturing"
Set rngia = Cells(i, 1).Find(What:=SearchIn(n), LookIn:=xlValues,
Lookat:=xlPart)
Thanks.
|