Count Merged Cells VBA Function - Help

[Matt888]

Hi,
I found the following function so I can count the merged cells in a specified range. When I use the function in the sheet e.g. =COUNTMERGED("A1:Z1") the returned count of merged cells is correct. When I use this formula in another macro (e.g. Range("G8").Formula = "=COUNTMERGED("A1:Z1") and copy to another range of cells the returned value is always #VALUE!

Any ideas why the formula is not resolving when entered as a formula in a range within a macro? Thanks in advance for your help.

Function COUNTMERGED(pWorkRng As Range) As Variant
Dim rng As Range
Set dt = CreateObject("Scripting.Dictionary")
For Each rng In pWorkRng
    If rng.MergeCells Then
        TempAddress = rng.MergeArea.Address
        dt(TempAddress) = ""
End If
Next
COUNTMERGED = dt.Count
End Function

 

[davesexcel]

Should it be?

Range("G8").Formula = "=COUNTMERGED(A1:Z1)"

 

[Matt888]

Should it be?

Range("G8").Formula = "=COUNTMERGED(A1:Z1)"

Sorry that's not the issue (I just forgot to include the final " in this thread).

If I enter the formula =COUNTMERGED("A1:Z1") in a cell in a worksheet it work fine and returns the correct number of merged cells in that range

When I apply the same formula to a range in my macro e.g. Range("G8").Formula = "=COUNTMERGED("A1:Z1")" it returns #VALUE! in cell G8....I cannot understand why the formula resolves correctly when input directly in the cell in the worksheet but resolves with #VALUE! error when effectively performing the same action by in my macro instead.

 

[davesexcel]

on the sheet it should be

=COUNTMERGED(A1:Z1)

in the macro it should be

Range("G8").Formula = "=COUNTMERGED(A1:Z1)"

 

[Matt888]

on the sheet it should be

=COUNTMERGED(A1:Z1)

in the macro it should be

Range("G8").Formula = "=COUNTMERGED(A1:Z1)"

Yes that is exactly as I have it. I seem to have narrowed down the issue but have no idea how to resolve....

The formula, once applied in the macro is copied down a range and then that range is filtered using autofilter to display any values >0. It appears to be the act of applying the filter that causes the COUNTMERGED formula to return a #VALUE! error. I have no idea why?!

Any thoughts?

 

[davesexcel]

You have been showing your formulas with quotes around the range, I'm showing the formulas with no quotes around the range.