VBA match function doesn't work when a cell is merged

[blue333]

Hi Everyone

I am working on a macro where I need find the column number which contains a particular text. However the noted cell is merged with others. I prefer to not unmerge the cells. Is there a way to make the match function work with merged cells? If match is not the way to go then I am open to other ways. In the end, I just need to find the column number where the text is located.

First attempt:

target_location=application.match("YTD",sheets(counter1).rows("7:7"),0)

Second attempt:

target_location=application.match("YTD",sheets(counter1).rows("7:8"),0)

The merged cell is rows 7 and 8 so I thought second attempt would work. No luck. The compiler ran an error at this line.

Any thoughts?

 

[Modesto73]

Is counter1 a worksheet variable? or just the name of the worksheet? If it is just the name then you need quotes around counter1 ("counter1") 7:7 should work even if there are merged rows

 

[blue333]

It's a variable. I have a loop that cycles through all the worksheets.

 

[Marcelo Branco]

This worked for me

Dim target_location As Long, counter1 As Long
counter1 = 9
target_location = Application.Match("YTD", Sheets(counter1).Rows("7:7"), 0)
MsgBox target_location

where in my 9th sheet (from left to right) F7 = YTD and F7 and F8 are merged

M.

 

[blue333]

This worked for me

Dim target_location As Long, counter1 As Long
counter1 = 9
target_location = Application.Match("YTD", Sheets(counter1).Rows("7:7"), 0)
MsgBox target_location

where in my 9th sheet (from left to right) F7 = YTD and F7 and F8 are merged

M.

its complaining of a type mismatch.

I have two identical sheets which are being cycled through. one has merged cells (V7 and V8) while other does not. the compiler stopped at the merged one.

Any thoughts?

 

[Marcelo Branco]

Is really V7 = YTD (not V8), no spaces, no extraneous characters?
To check try in an empty cell
=V7="YTD"

M.

 

[dmt32]

Hi,
Application.Match returns an error if value not found which presumably, is why your code is errors.

You can manage this by using the IsError function however, your variable target_location will need to be declared as Variant data type.

     Dim target_location As Variant
    target_location = Application.Match("YTD", Sheets(counter1).Rows("7:7"), 0)
    If Not IsError(target_location) Then
    
' match found do stuff
    
    End If

Where Match is found you can coerce your variable target_location to correct data type using a type conversion function

MsgBox CLng(target_location)

Hope Helpful


Dave

 

[blue333]

Is really V7 = YTD (not V8), no spaces, no extraneous characters?
To check try in an empty cell
=V7="YTD"

M.

you are a genius! the value for the merged cell is stored in v7 and not v8.

it works now!

thanks for your help!!!

 

[Marcelo Branco]

You are very welcome. Glad to help

M.