Cells.Find command. How to continue if the What:= value is not found

A

alzasp

New Member
Joined
Apr 25, 2017
Messages
13
I have the following set of commands/code in a macro that can be run on different spreadsheets. How can if make the macro skip the set of commands to the next group if the value is not found, i.e. if the "0243" is not in the spreadsheet I want the macro to next group command and try to find the "0245" ?

Cells.Find(What:="0243", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(2, 0).Select
Selection.End(xlDown).Select
ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC4,spec_hrs,11,FALSE)"
Selection.Copy
Selection.End(xlDown).Select
ActiveCell.Offset(-1, 0).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False

Cells.Find(What:="0245", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC4,sys_code,25,FALSE)"
Selection.Copy
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
alzasp,

Try like....

Code: 
[COLOR=#0000ff]On Error GoTo Try0245[/COLOR]
Cells.Find(What:="0243", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(2, 0).Select
Selection.End(xlDown).Select
ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC4,spec_hrs,11,FALSE)"
Selection.Copy
Selection.End(xlDown).Select
ActiveCell.Offset(-1, 0).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False




[COLOR=#0000ff]Try0245:[/COLOR]
[COLOR=#0000ff]On Error GoTo 0[/COLOR]
Cells.Find(What:="0245", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC4,sys_code,25,FALSE)"
Selection.Copy
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False

Hope that helps.
 
Upvote 0
This works on the first group but when it moves to the next group I get an error 91 and the red italics below is highlighted when I hit debug. Any suggestions? I added some more of my macro so you can see what I am trying to do.

 On Error GoTo Try0211
Cells.Find(What:="0210", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC4,sys_code,42,FALSE)"


Try0211:
On Error GoTo Try0230
Cells.Find(What:="0211", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC4,sys_code,19,FALSE)"
Selection.Copy
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False



Try0230:
On Error GoTo Try0240
Cells.Find(What:="0230", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC4,sys_code,19,FALSE)"
Selection.Copy
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False



Try0240:
On Error GoTo Try0241
Cells.Find(What:="0240", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
On Error GoTo Try0241
ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC4,sys_code,3,FALSE)+VLOOKUP(RC4,sys_code,4,FALSE)+VLOOKUP(RC4,sys_code,5,FALSE)+VLOOKUP(RC4,sys_code,6,FALSE)+VLOOKUP(RC4,sys_code,12,FALSE)"
Selection.Copy
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
 
Upvote 0
Try like...

Code: 
Set myfind = Cells.Find(What:="0210", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
If Not myfind Is Nothing Then
myfind.Activate
Else
GoTo Try0211
End If


ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC4,sys_code,42,FALSE)"




Try0211:


Set myfind = Cells.Find(What:="0211", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
If Not myfind Is Nothing Then
myfind.Activate
Else: GoTo Try0230
End If


ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC4,sys_code,19,FALSE)"
Selection.Copy
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False






Try0230:


Set myfind = Cells.Find(What:="0230", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
If Not myfind Is Nothing Then
myfind.Activate
Else
GoTo Try0240
End If


ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC4,sys_code,19,FALSE)"
Selection.Copy
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False






Try0240:


Set myfind = Cells.Find(What:="0240", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
If Not myfind Is Nothing Then
myfind.Activate
Else
GoTo Try0241
End If




ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC4,sys_code,3,FALSE)+VLOOKUP(RC4,sys_code,4,FALSE)+VLOOKUP(RC4,sys_code,5,FALSE)+VLOOKUP(RC4,sys_code,6,FALSE) +VLOOKUP(RC4,sys_code,12,FALSE)"
Selection.Copy
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Select range using Endx1Down
Replies
4
Views
406
Peter_SSs
Peter_SSs
G
  • Question
How can i extract the correct leave time on this code?
Replies
1
Views
402
rlv01
R
A
  • Solved
Help with "Range(Selection, Selection.End(xlDown)).Select" that includes empty cell please
Replies
3
Views
1K
Akuini
Akuini
B
  • Question
Finding excluded values in excel
Replies
0
Views
243
bcmk29
B
C
  • Question
Copy, paste data from one sheet to another based on specific text
Replies
1
Views
655
OaklandJim
O

Forum statistics

Threads
1,216,477
Messages
6,130,879
Members
449,603
Latest member
dizze90

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top