• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic find using variables in the What clause.

O

OrionDon

New Member
I am trying to search for values from sheet1 on in sheet2 but I getting an error: Run-Time error '91' Object variable or with block variable not set.

The code I am use is below. and I am using Office 2010

[pre]
Code: 
Sub Member_Search()
'
' Member_Search Macro
'

'
Dim SecID As String
Dim MemID As String

Dim bFound As Boolean

Sheets("Sheet1").Select

Range("A2").Select

ActiveCell.Range("B1").Select

SecondaryID = ActiveCell.Value

ActiveCell.Offset(0, 1).Select

MemID = ActiveCell.Value

Sheets("Sheet2").Select

Range("A2").Select

bFound = Cells.Find(What:=SecondaryID, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If bFound Then
'  Do something
End If

End Sub
[/pre]
 
On the
Code: 
Find line, you're trying to set bFound equal to something, but at the end of line you're saying to activate the cell. You can't do both. If the value in the cell is what you need to store in bFound, macro should be (note I simplified first part to reduce # of selections)

[pre]Sub Member_Search()
'
' Member_Search Macro
'

'
Dim SecondaryID As String
Dim MemID As String

Dim bFound As Boolean

'Don't need to select the cells, can reference them directly
With Sheets("Sheet1")
SecondaryID = .Range("B1").Value
MemID = .Range("C1").Value
End With

'Again, we can reference Sheet2 directly
With Sheets("Sheet2")
bFound = .Cells.Find(What:=SecondaryID, After:=.Range("A2"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Value
End With

If bFound Then
'  Do something
End If

End Sub
[/pre]
 
I'm still getting the Run-Time error on the .


Run-Time error '91'


Object variable or with block variable not set.


Even when I replace the find code with the code below, I still get the Run-Time error.


Cells.Find(What:="200050330", After:=ActiveCell, LookIn:=xlFormulas, _

LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _

MatchCase:=False, SearchFormat:=False).Activate
 
What exactly are you trying to do/check? The Find Method is going to find the cell, if it exists, that equals your find string. It looks like you're then checking the value...do you just want to know if the value exists?

Perhaps this is more what you are after?

[pre]
Code: 
Sub Member_Search()
'
' Member_Search Macro
'

'
Dim SecondaryID As String
Dim MemID As String
Dim foundCell As Range

Dim bFound As Boolean

'Don't need to select the cells, can reference them directly
With Sheets("Sheet1")
SecondaryID = .Range("B1").Value
MemID = .Range("C1").Value
End With

'Again, we can reference Sheet2 directly
With Sheets("Sheet2")
Set foundCell = .Cells.Find(What:=SecondaryID, After:=.Range("A2"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
End With

'Check if we found the cell
If Not (foundCell Is Nothing) Then
'  Do something
End If

End Sub
[/pre]
 
Hi, OrionDon!


As Luke M asked, what are you exactly trying to do? I tweaked a little your original code to display the address of found item, if found, but I'm not sure if that's your goal.


Check the updated code:

-----

[pre]
Code: 
Sub Member_Search()
'
' Member_Search Macro
'

'
Dim SecID As String
Dim MemID As String

'    Dim bFound As Boolean
Dim sFound As String

Sheets("Hoja1").Select

Range("A2").Select

ActiveCell.Range("B1").Select

SecondaryID = ActiveCell.Value

ActiveCell.Offset(0, 1).Select

MemID = ActiveCell.Value

Sheets("Hoja2").Select

Range("A2").Select

sFound = Cells.Find(What:=SecondaryID, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Address

If sFound <> "" Then
MsgBox SecondaryID & " found in 2nd sheet at address " & sFound
'  Do something
End If

End Sub
[/pre]
-----


Regards!
 
I have a list of values in Sheet1, and I need to search sheet2 for the values. then create a third sheet with the results.


Value1 found

Value2 not found

Value3 found
 
How about this then? It will loop through all the values on Sheet 1, check if secondary ID is on Sheet2, and list results on Sheet3.

[pre]
Code: 
Sub Member_Search()

Dim SecondaryID As String
Dim MemID As String
Dim foundCell As Range
Dim lastRow As Long
Dim recordCount As Long

Dim bFound As Boolean

'Don't need to select the cells, can reference them directly
With Sheets("Sheet1")
lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With
Application.ScreenUpdating = False
recordCount = 1
For i = 1 To lastRow
With Sheets("Sheet1")
SecondaryID = .Cells(i, 2).Value
MemID = .Cells(i, 3).Value
End With

'Again, we can reference Sheet2 directly
With Sheets("Sheet2")
Set foundCell = .Cells.Find(What:=SecondaryID, After:=.Range("A2"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
End With

'Check if we found the cell
With Worksheets("Sheet3")
.Cells(recordCount, "A").Value = MemID
If Not (foundCell Is Nothing) Then
.Cells(recordCount, "B").Value = "Found"
Else
.Cells(recordCount, "B").Value = "Not Found"
End If
End With
Next i
Application.ScreenUpdating = True
End Sub
[/pre]
 
Hi, OrionDon!


You don't need VBA for doing that but you can use it. Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/Dynamic%20find%20using%20variables%20in%20the%20What%20clause.%20%28for%20OrionDon%20at%20chandoo.org%29.xlsm


Formula solution (Hoja4):


a) Search for values, column A in worksheet Sheet1

b) Search into values, column A in worksheet Sheet2

c) Status values, column A:B in worksheet Sheet3

A2: =SI(Hoja1!$A2<>"";Hoja1!$A2;"") -----> in english: =IF(Sheet1!$A2<>"",Sheet1!$A2,"")

B2: =SI(Hoja1!$A2<>"";SI(ESNOD(COINCIDIR(A2;Hoja2!A:A;0));"Not found";"Found");"") -----> in english: =IF(Sheet1!$A2<>"",IF(ISNA(MATCH(A2,Sheet2!A:A,0)),"Not found","Found"),"")

C2: =SI(B2="Found";COINCIDIR(A2;Hoja2!A:A;0);"") -----> in english: =IF(B2="Found",MATCH(A2,Hoja2!A:A,0),"")


VBA solution (Hoja3):


a) Define 3 dynamic named ranges (not necessary but recommended, you can use them in the formula solution version)

SearchForList: =DESREF(Hoja1!$A$1;;;CONTARA(Hoja1!$A:$A);CONTARA(Hoja1!$1:$1)) -----> in english: =OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

SearchIntoList: =DESREF(Hoja2!$A$1;;;CONTARA(Hoja2!$A:$A);CONTARA(Hoja2!$1:$1)) -----> in english: =OFFSET(Sheet2!$A$1,,,COUNTA(Sheet2!$A:$A),COUNTA(Sheet2!$1:$1))

StatusTable: =DESREF(Hoja3!$A$1;;;CONTARA(Hoja3!$A:$A);CONTARA(Hoja3!$1:$1)) -----> in english: =OFFSET(Sheet3!$A$1,,,COUNTA(Sheet3!$A:$A),COUNTA(Sheet3!$1:$1))


b) Place this code in any module and run it:

-----

[pre]
Code: 
Sub Member_Search_New()
' constants
Const ksWSSearchFor = "Hoja1" '"Sheet1"
Const ksSearchFor = "SearchForList"
Const ksWSSearchInto = "Hoja2" '"Sheet2"
Const ksSearchInto = "SearchIntoList"
Const ksWSStatus = "Hoja3" '"Sheet3"
Const ksStatus = "StatusTable"
' declarations
Dim rngF As Range, rngI As Range, rngS As Range, c As Range
Dim lFor As Long, lInto As Long, lFound As Long, lNotFound As Long
' start
Set rngF = Worksheets(ksWSSearchFor).Range(ksSearchFor)
Set rngI = Worksheets(ksWSSearchInto).Range(ksSearchInto)
Set rngS = Worksheets(ksWSStatus).Range(ksStatus)
With rngS
If .Rows.Count > 1 Then Range(.Rows(2), .Rows(.Rows.Count)).ClearContents
End With
' process
lInto = rngI.Rows.Count - 1
lFound = 0
lNotFound = 0
With rngF
For lFor = 2 To .Rows.Count
Set c = rngI.Find(.Cells(lFor, 1).Value, _
rngI.Cells(1, 1), xlValues, xlWhole, , xlNext, True)
rngS.Cells(lFor, 1).Value = .Cells(lFor, 1).Value
If c Is Nothing Then
lNotFound = lNotFound + 1
rngS.Cells(lFor, 2).Value = "Not found"
Else
lFound = lFound + 1
rngS.Cells(lFor, 2).Value = "Found"
rngS.Cells(lFor, 3).Value = c.Address(False, False)
End If
Next lFor
End With
' end
MsgBox lFor - 2 & " values searched for in " & ksWSSearchFor & "!" & rngF.Address & vbCrLf & _
lInto & " values to search into in " & ksWSSearchInto & "!" & rngF.Address & vbCrLf & _
lFound & " values found in " & ksWSStatus & "!" & rngF.Address & vbCrLf & _
lNotFound & " values found in " & ksWSStatus & "!" & rngF.Address & vbCrLf, _
vbApplicationModal + vbInformation, "Summary"
Set rngS = Nothing
Set rngI = Nothing
Set rngF = Nothing
Beep
End Sub
[/pre]
-----


Just advise if any issue.


Regards!
 
You must log in or register to reply here.
Back
Top