Find a text in a merged cell and select it

[nikolaikolev]

Hi guys,
i am struggling to make a macro that will find the word "Yes" in column A and select it.
The word "Yes" is in column A but on that row where the word is, all cells from column A to column H are merged (as in the attached file).
Any ideas how can i possibly do that ?

 

[Joe4]

Merged cells are a huge pain in the neck that cause all sort of problems with things like sorting and VBA.
It is highly advised that you remove them.

You can achieve the exact same visual effect without all the issues by using the "Center Across Selection" formatting option instead.
See: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

If you do this, then you should have no issues making the VBA code that you want.
You can use the Macro Recorder to get most of the code that you need.

 

[Rick Rothstein]

I agree with Joe about avoiding using merged cells; however, to answer you question, this should work...

Columns("A:H").Find("yes", Cells(Rows.Count, "A"), xlValues, xlWhole, , xlNext, False).Select

Note I had to specify all the columns in your merge to make it work.

 

[nikolaikolev]

I agree with Joe about avoiding using merged cells; however, to answer you question, this should work...

Columns("A:H").Find("yes", Cells(Rows.Count, "A"), xlValues, xlWhole, , xlNext, False).Select

Note I had to specify all the columns in your merge to make it work.

Thanks mate the code work just fine but the problem is that it finds the first occurance for the word Yes that is located in E45, but the word Yes i am looking for is in A143

 

[nikolaikolev]

Thanks mate the code work just fine but the problem is that it finds the first occurance for the word Yes that is located in E45, but the word Yes i am looking for is in A143

Is there any way to make it search for the word from lets say row 80 downwards ?

 

[Joe4]

Thanks mate the code work just fine but the problem is that it finds the first occurance for the word Yes that is located in E45, but the word Yes i am looking for is in A143

That is precisely one of problems with using merged cells! If you have merged columns A:H, you cannot tell it to only look at column A and not column H, as they are all merged!
That is why the other option I suggested would work better.

Is there any way to make it search for the word from lets say row 80 downwards ?

You can search any range that you define. It does not have to be a full column.
You can search column A, started in row 80, down to the last row with data in column A.
You can define that range like this:

Dim lr as Long
Dim rng as Range

'Find last row with data in column A
lr = Cells(Rows.Count, "A").End(xlUp).Row

'Define range
Set rng = Range("A80:A" & lr)

Note that you still may have issues if you do not rectify the merged cells issues mentioned above.

 

[nikolaikolev]

That is precisely one of problems with using merged cells! If you have merged columns A:H, you cannot tell it to only look at column A and not column H, as they are all merged!
That is why the other option I suggested would work better.


You can search any range that you define. It does not have to be a full column.
You can search column A, started in row 80, down to the last row with data in column A.
You can define that range like this:

Dim lr as Long
Dim rng as Range

'Find last row with data in column A
lr = Cells(Rows.Count, "A").End(xlUp).Row

'Define range
Set rng = Range("A80:A" & lr)

Note that you still may have issues if you do not rectify the merged cells issues mentioned above.

thanks pal

 

[Joe4]

You are welcome.