vba to find text then find first blank cell to the right

Appreciate someone's help please to find the word Red and then find the first empty cell to the right and select that cell

Many thanks
JagwarmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JagwarmanAuthor Commented:
Sorry I need to amend that slightly.

I need to find the word Red and the select to the right the last non empty cell

So lets say Red is in H3 and cells H4,H5,H6 have data and H7 is blank I need to select H6
If Red is in H3 and cells H4,H5,H6,H7,H8 have data and H9 is blank I need to select H8

Thanks
0
Saqib Husain, SyedEngineerCommented:
If you know that H4 is never blank then you can only use

Cells.Find("Red").End(xlToRight).Offset(, 1).Select

If that is not the case then use

    Dim redc As Range
    Set redc = Cells.Find("Red")
    If redc.Offset(, 1) = "" Then
        redc.Offset(, 1).Select
    Else
        redc.End(xlToRight).Select
    End If
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JagwarmanAuthor Commented:
hi Saqib Husain, Syed.

It's very early in the morning and I am clearly not awake. I am looking for it to move to the right so my post was incorrect I should have said,  ... H3 and cells I3,J3,K3 have data and L3 is blank I need to select K3

It looks like you understood what I was asking for although,

in your first scenario that takes me to L3 [which is a blank cell and not K3 which is the last non blank cell.

But, your second scenario works it takes me to K3

So many thanks for that.
0
Saqib Husain, SyedEngineerCommented:
Oh I was basing the selection on the question which said "empty" cell.

The correct syntax for this case should be

Cells.Find("Red").End(xlToRight).Select
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.