If/Then/Else, Search & Set Active Cell VBA Help

[Baiano42]

Good morning,

I've been trying to make an If/Then search macro that would search for a value and perform a set of tasks if it finds the cell with the information needed. Else, it would do a follow-up search for a different value and perform a different set of functions. While searching the forums, I haven't had much luck with finding guidance with this problem, so I was hoping that I could get some help here.

Being fairly new to coding, here is what I have come up with so far:


Sub IfThenSearch()

Set SearchRange = Range("A1:A10")
FindWhat = strSearchQuery
'First Search Criteria, want to find the first row with "A"
Set FoundCellsA = Cells.Find(what:="A", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
'Second Search Criteria, want to find the first row with "Z"
Set FoundCellsB = Cells.Find(what:="Z", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

'If first search is finds nothing, then move to second search
If FoundCellsA Is Nothing Then
'If second search finds nothing, exit sub
If FoundCellsB Is Nothing Then
Exit Sub
'If second search does find "Z", then Macro
Else
MACRO
End If
'If first search does find "A", then Macro
Else
MACRO
End If
End Sub

[Artik]

Try
Sub IfThenSearch_1()

Set SearchRange = Range("A1:A10")




'First Search Criteria, want to find the first row with "A"
Set FoundCellsA = SearchRange.Find(what:="A", After:=SearchRange.Cells(1), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)


'Second Search Criteria, want to find the first row with "Z"
Set FoundCellsB = SearchRange.Find(what:="Z", After:=SearchRange.Cells(1), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)


'If first search is finds nothing, then move to second search
If FoundCellsA Is Nothing Then
'If second search finds nothing, exit sub
If FoundCellsB Is Nothing Then
Exit Sub
'If second search does find "Z", then Macro
Else
MACRO
End If
'If first search does find "A", then Macro
Else
MACRO
End If
End Sub
Artik

[Baiano42]

@Artik:
When using that method and imputing the macros, I receive the following error:
Run-time error '91':
Object variable or With block variable not set

Here's the code with the Macros set:

Set SearchRange = Range("A1:A10")

'First Search Criteria, want to find the first row with "A"
Set FoundCellsA = SearchRange.Find(what:="A", After:=SearchRange.Cells(1), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)




'Second Search Criteria, want to find the first row with "Z"
Set FoundCellsB = SearchRange.Find(what:="Z", After:=SearchRange.Cells(1), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)




'If first search is finds nothing, then move to second search
If FoundCellsA Is Nothing Then
If FoundCellsB Is Nothing Then
Cells.Find(what:="A", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(2, 0).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("AB2").Select
ActiveSheet.Paste
Cells.Find(what:="Zed", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(2, 0).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("AA2").Select
ActiveSheet.Paste
Range("AD2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"
Range("AD2").Select
Selection.AutoFill Destination:=Range("AD2:AD1000")
Range("AD2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("AB2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("AA1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "ZedAlph"
Range("AB1").Select
ActiveCell.FormulaR1C1 = "AlpZed"
Range("A:Z,AC:AD").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Else
Cells.Find(what:="Z", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(2, 0).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("AB2").Select
ActiveSheet.Paste
Cells.Find(what:="Zed", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(2, 0).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("AA2").Select
ActiveSheet.Paste
Range("AD1").Select
ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"
Range("AD1").Select
Selection.AutoFill Destination:=Range("AD2:AD1000")
Range("AD2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("AB2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("AA1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "ZedAlph"
Range("AB1").Select
ActiveCell.FormulaR1C1 = "AlphZed"
Range("A:Z,AC:AD").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select


End If
Else
Exit Sub
End If

[Baiano42]

@Artik:
I think that I found the issue, I put it as A1:A10, rather than A1:O1, adjusting that and the code how you had it made it work. Thank you sir!


Sub IfThenSearch_1()

Set SearchRange = Range("A1:O1")


'First Search Criteria, want to find the first row with "A"
Set FoundCellsA = SearchRange.Find(what:="A", After:=SearchRange.Cells(1), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)


'Second Search Criteria, want to find the first row with "Z"
Set FoundCellsB = SearchRange.Find(what:="Z", After:=SearchRange.Cells(1), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)


'If first search is finds nothing, then move to second search
If FoundCellsA Is Nothing Then
'If second search finds nothing, exit sub
If FoundCellsB Is Nothing Then
Exit Sub
'If second search does find "Z", then Macro
Else
MACRO
End If
'If first search does find "A", then Macro
Else
MACRO
End If

End Sub