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. |
Using Array variable in FIND function
JMB,
After your clarification it is now working okay! Apparently, I was not declaring the array properly. I choose to go with your first suggestion, though: ..... Dim SearchIn(1 To 9) As String SearchIn(1) = "Employment (NAICS), Government (Thousands)" SearchIn(2) = "Employment (NAICS), Manufacturing (Thousands)" .... Thank you. "JMB" wrote: 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. |
All times are GMT +1. The time now is 07:44 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com