How to deal with an Empty return using selection.Find?

gruntingmonkey

Active Member
Joined
Mar 6, 2008
Messages
414
I have some code which matches up a cell and moves some commentary from one workbook to another. The issue I'm having is handling the selection.find when there is no match. I want to just move on to the next row (I'll put a count in later). but I cant find a way to do this. I tried On error, but it doesn't recognise a non match as an error and therefore debugs rather then moving on. Does anyone have any ideas?

VBA Code:
lastrow = Cells(Rows.Count, "A").End(xlUp).row
rowno = 2

Do Until rowno > lastrow

Workbooks(Filename).Activate
Comment = Cells(rowno, 11).Value
Identifier = Cells(rowno, 19).Value

If Comment <> "" Then
Workbooks(ThisWorkbook.Name).Worksheets("BH Data").Activate
Columns("R:R").Select
Selection.Find(What:=Identifier, After:=ActiveCell, LookIn:=xlFormulas, lookat:=xlPart, SearchOrder:= _
        xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) _
        .Activate
    Matchrow = ActiveCell.row
Cells(Matchrow, 10).Value = Comment

Else
End If
rowno = rowno + 1
Loop
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,512
Office Version
  1. 2019
Platform
  1. Windows
Hi
untested but try this update to your code & see if does what you want

Rich (BB code):
 Dim FoundCell   As Range
    Dim LastRow     As Long, rowno As Long
    Dim wsBHData    As Worksheet, ws As Worksheet
    
    
    Set wsBHData = ThisWorkbook.Worksheets("BH Data")
    
    Set ws = Workbooks(FileName).Worksheets("Sheet1")
    
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    rowno = 2
    
    Do Until rowno > LastRow
        
        Comment = ws.Cells(rowno, 11).Value
        Identifier = ws.Cells(rowno, 19).Value

        If Comment <> "" Then
                Set FoundCell = wsBHData.Columns("R:R").Find(What:=Identifier, LookIn:=xlFormulas, lookat:=xlPart, _
                                                             SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                                            MatchCase:=False, SearchFormat:=False)
                If Not FoundCell Is Nothing Then wsBHData.Cells(FoundCell.Row, 10).Value = Comment
        End If
        rowno = rowno + 1
        Set FoundCell = Nothing
    Loop

Change Sheet1 name shown in BOLD as required

Dave
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,499
Messages
5,636,688
Members
416,935
Latest member
Atulcp

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
Top