VBA - Do stuff based on a value in a merged cell

[Alexlin]

Hi Thanks for visiting my post. I have a task to insert a row based on the value of a cell.
However some cells are merged cells, some unmerged. I have tried code below. But it does not work with "type mismatch" error:-
I do not know the exact location of the merged cell.
Any expert knows how to modify it?
----------------------------------
Dim rng2 As Range

For Each rng2 In Range("A5", "J30")
If rng2.value="Myvalue" Then
rng2.entirerow.insert

Elseif rng2.MergeCells Then
if rng2.MergeArea.Value = "Myvalue" Then
rng2.entirerow.insert
End If
next
------------------------
Thanks v. much

 

[Joe4]

Merged cells cause all sort of issues for Excel, especially for things like VBA. As such, it is HIGHLY recommended that you not use them at all.
If you are just merging cells across a row, you can get the same visual effect by using the "Center Across Selection" formatting option, which does not cause any issues for VBA, sorting, etc.
See here: https://www.atlaspm.com/toms-tutori...er-across-selection-instead-of-merging-cells/

 

[Alexlin]

Hi Joe thanks for ur reply. I know center across selections method
But for my case, the cells in my template have been merged and I can’t change.
I’m not merging anything. I’m copying some source data in a merged cell to another merged cell in the template file

 

[Joe4]

VBA does not like merged cells at all.
I have seen people sometimes able to work around it, but in order do that, we probably need to know the exact location of the merged cells, so we can try to recreate the scenario to test it out.

Or, if you could upload your file to a file share site so people can see what you are working with.
I cannot download files from my current location, but can from my home, which is where I will be tonight.