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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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

R
  • Question Question
cells.find error handling
2
Replies
13
Views
560
jolivanes
jolivanes
T
  • Question Question
Apply macro to all work sheets
Replies
3
Views
341
MARK858
MARK858
M
  • Question Question
How To Loop Macro
Replies
6
Views
499
rlv01
R
SlinkRN
  • Solved
Handling Multiple Errors within one loop
Replies
7
Views
251
SlinkRN
SlinkRN
M
  • Question Question
Macro - find (control+f) value copied earlier in process
Replies
9
Views
317
Alex Blakenburg
A

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,631
Latest member
a_potato

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