populate cells within merge area

[David]

for charting purposes i need to populate cells within a merged area in my code.
If myrng is the merged area and myrng.cells(1) contains mydate then...
I've tried

dim cl as range
dim myrng as range
set myrng = Range("MergedRange")

for each cl in myrng.cells
cl.value = cl.MergeArea.cells(1).value
next cl
unfortunately, all cells in the merged range remain blank except for cells(1)

I've noticed that I can manually populate all cells in a merged range by
copying a value and then using pasteSpecial/formulas. In lieu of any better
sugestions i'll write code around this principle
Thanks

 

[Matthew Herbert]

David,

I'm not quite sure that I follow what you are trying to do because there is
not enough detail in your post. For example, I don't know what the named
range "MergedRange" refers to, and I don't know which cell(s) within
"MergedRange" are merged (if any); however, so as to not assume anything, did
you check out the Excel help for Merge Cell?

"When you merge two or more adjacent horizontal or vertical cells, the cells
become one larger cell that is displayed across multiple columns or rows.
When you merge multiple cells, the contents of only one cell (the upper-left
cell for left-to-right languages, or the upper-right cell for right-to-left
languages) appear in the merged cell (merged cell: A single cell that is
created by combining two or more selected cells. The cell reference for a
merged cell is the upper-left cell in the original selected range.)."

I never work with (or use) merged cells because they are a
selection/navigation nightmare (when doing quick movements via Ctrl+Arrow Key
or Ctrl+Shift+Arrow Key); however, I've listed some code below that may be
useful to you. You can Step Into the code via F8 (repeatedly) to see how the
code is evaluated. My results are being printed to the Immediate Window
(Ctrl+g or View|Immediate Window) via the Debug.Print statement.

Best,

Matthew Herbert

Sub TestMergedCells()
Dim Rng As Range
Dim rngCell As Range
Dim rngMergeAreas As Range

Range("A1:B1").MergeCells = True
'only Range("A1").value will set the value property
Range("A1").Value = "A1"
Range("B1").Value = "B1"

Range("A3:C3").MergeCells = True
'only Range("A3").value will set the value property
Range("A3").Value = "A3"
Range("B3").Value = "B3"
Range("C3").Value = "C3"

Range("B5:C5").MergeCells = True
'only Range("B5").value will set the value property
Range("B5").Value = "B5"
Range("C5").Value = "C5"

Set Rng = Range("A1:C5")

For Each rngCell In Rng.Cells

Set rngMergeAreas = rngCell.MergeArea

'continue if the cell is merged
If rngMergeAreas.MergeCells Then

'no need to go through each cell within MergeArea
If rngMergeAreas(1).Address = rngCell.Address Then

'any of the variations below will work
'Debug.Print rngMergeAreas.Cells(1).Address
'Debug.Print rngMergeAreas.Cells(1, 1).Address
With rngMergeAreas(1)
Debug.Print "Address:"; .Address; "|Value:"; .Value
End With
End If
End If
Next rngCell

End Sub

 

[David]

Mathew,
Thanks for your response
Let me ask the question a different way:
Each cell in range("A1:C1") contains the word "dog"
If I merge cells A1:A3 I finish up with the following:
A1 contains "dog". B1 is null. C1 is null as expected
If I now enter "dog" in cell A2 and Edit\copy from A2
Then Edit\PasteSpecial\Formulas to the merged range("A1:C1")
I achieve the result that I require, ie: A1 = "dog", B1 = "dog, C1 = "dog"!
Note that the range is still merged
Thus I have populated each cell in a merged range manually.
To do this operation with code I can follow the same process steps
I have now done this and am getting the required result
I thought that there was probably a more straightforward way of doing it?
Obviously, I'm using a temporary holding cell for my copy paste operations

 

[David]

Typing error in my last post:
"If I merge cells A1:A3 I finish up with the following:"
should read:
"If I merge cells A1:C3 I finish up with the following:"
Cheers

 

[David]

Doh!
should read:
"If I merge cells A1:C1 I finish up with the following:"

 

[Matthew Herbert]

David,

That is some interesting behavior (which I'm not so sure that Microsoft
intended); however, you discovered something unique about merged cells (or
the .PasteSpecial xlPasteFormulas operation). I tried a number of the range
object properties to acheive the same result, but was unsuccessful. So, I
would stick with your .PasteSpecial xlPasteFormulas operation.

Best,

Matt