asked on

Search for term in first row of current data list using VBA

Dear Experts:

I got an Excel makro that should be preceded with a code snippet. Here are the requirements for this snippet:

(1) Search for the Word 'Graphic' (Search only in the first row of the current worksheet, Match Entire Cell Contents) ,
(2) If not found, exit sub
(3) if found, go down that column till the macro hits the first occurrence of a non-blank cell, select this first occurrence.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

byundt

Are the cells in the Graphic column the result of formulas or data entered by the user? I ask because you may want to ignore cells that contain an empty string returned by a formula. If so, consider:

Sub GraphicTermFinder()
Dim c As Range, d As Range
With ActiveSheet.Range("1:1")
    Set c = .Find(What:="Graphic", LookIn:=xlFormulas, LookAt:=xlWhole, MatchCase:=False)
End With

If Not c Is Nothing Then
    Set d = Range(c, Cells(Rows.Count, c.Column)).Find("?*", LookIn:=xlValues, LookAt:=xlWhole)
    If Not d Is Nothing Then
        If d.Row <> 1 Then d.Select
    End If
End If
End Sub

Open in new window

Note that the Find method will ignore any cells that are hidden by a filter.

Andreas Hermle

ASKER

Hi rgonzo,

great this did the trick. Thank you very much for your professional help.

Regards, andreas

Andreas Hermle

ASKER

Hi byundt:

Thank you very much for your professional help.

I should have refreshed before awarding points.

Excellent point, thank you for bringing this to my attention.

Regards, Andreas