How to check if all headers are present in worksheet

[ThrottleWorks]

Hi,

I am using below mentioned code to check if all the headers are present in particular worksheet.

BrSht.Select
Set TempRng = DivSht.Range(DivSht.Cells(2, 4), DivSht.Cells(40, 4))
For Each Rng In TempRng
On Error Resume Next
Cells.Find(What:=Rng, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, MatchCase:=False, SearchFormat:=False).Activate
On Error GoTo 0
If ActiveCell.Value <> Rng Then
MsgBox Rng & " not present in BR sheet"
End
End If
Next

But in this code I need to select every header to check. How can I check all the headers without selecting any cell.

Can anyone please help me in this.

 

[Marc L]

Hi !

No need On Error even using Find method :
just test result like within its inner VBA help sample !

Or you can use Match function, both ways yet explained
in some of your previous posts …

Yes, no need to select, better is to use a cell reference like Range, Cells, …
or via a Range variable object like again within Find inner VBA help sample !
​

 

[ThrottleWorks]

Hi @Marc L sir, thanks a lot for the help. Sure, I will check my previous posts. Have a nice day ahead.

 

[Marc L]

If still hang, join a sample workbook …
​

 

[ThrottleWorks]

Hi @Marc L , sure sir. Good night. Have a nice weekend.

 

[shrivallabha]

That On Error Bit is for .Activate part. As Marc has specified, select and activate are not required most of the times. Something like following should work for you:

Dim SearchRng As Range 'Add this to declarations
Set TempRng = DivSht.Range(DivSht.Cells(2, 4), DivSht.Cells(40, 4))
For Each Rng In TempRng
    Set SearchRng = BrSht.Cells.Find(What:=Rng, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlWhole, MatchCase:=False, SearchFormat:=False)
    If SearchRng Is Nothing Then
        MsgBox Rng.Value & " not present in BR sheet"
    End If
Next

 

[ThrottleWorks]

Hi @ shrivallabha , thanks a lot for the help. I am checking this and will revert with details. Have a nice weekend.