Macro for copying contents in active cell and searching another sheet for that copied value

[MNik]

Hi All,

I'm trying to put together a macro that will copy the value from column H in same row of my active cell, then search another sheet for that value. If the value is found, I would like it to highlight the entire row, if not, then I would like a message box to return "Not Found"

Here is what I have so far.

Sub SearchTest()


Rng = ActiveCell.Value

Application.ScreenUpdating = False


    Sheets("Open but Delinquent").Select
    Columns("H:H").Select
        Cells.Find(What:=Rng, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
   
      
     ActiveCell.Select
        Selection.EntireRow.Select
           
   


Application.ScreenUpdating = True


End Sub

Currently, it will successfully run if it finds a match. If it does not find a match it provides a "Runtime error '91: Object variable or With block variable not set" if the value is not found on the second sheet. Is there a way for it to search using the value in column H based on the row I am in without needing to be in that cell? How can I re-write to stop providing an error when a matching value is not found?

Thanks in advance!

 

[offthelip]

try this:

Sub SearchTest()

rowno = ActiveCell.Row

Rng = Range("H" & rowno)


Application.ScreenUpdating = False


    Sheets("Open but Delinquent").Select
    Columns("H:H").Select
On Error GoTo errorhandle:
        Cells.Find(What:=Rng, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
   
      
     ActiveCell.Select
        Selection.EntireRow.Select
           
   


Application.ScreenUpdating = True

Exit Sub
errorhandle:
MsgBox (" Not Found")
Application.ScreenUpdating = True
End Sub

 

[MNik]

Thank you for you help! These additions helped it worked nearly flawlessly. I did make a few tweaks and I'm 99% of the way there.

One quirk that I noticed is that when a matching value is found, it does highlight the row but it doesn't bring me to the highlighted row, meaning you may have to scroll to find it. Any tips on how I could tweak it to take the me to the highlighted row?

Here is my updated version:

Sub SearchTest()

'
'Copy cell "E" in current row and search for value in OBD
'

rowno = ActiveCell.Row

rng = Range("E" & rowno)


Application.ScreenUpdating = False


    Sheets("Open but Delinquent").Select
    Columns("H:H").Select
On Error GoTo errorhandle:
        Cells.Find(What:=rng, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

      
    ActiveCell.Select
    Selection.EntireRow.Select
   
   
Application.ScreenUpdating = True

'
'If not found
'

Exit Sub
errorhandle:
MsgBox ("Not Found")
Sheets("Completed Shipments").Select


Application.ScreenUpdating = True


End Sub

 

[offthelip]

try deleting the:

Application.ScreenUpdating = False

and the

Application.ScreenUpdating = TRUE

I never use these, I find if you write good fast macros it is really uncessary and can cause problems, specially when the macro crashes out. ( you are left with screen NOT updating!!)

 

[MNik]

Yep, that did the trick. I'm new to working with macros, so I appreciate the help and tip immensely. Thanks again!