Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
populate cells within merge area
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
populate cells within merge area
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
populate cells within merge area
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 "Matthew Herbert" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
populate cells within merge area
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" wrote: 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 "Matthew Herbert" wrote: 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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
populate cells within merge area
Doh!
should read: "If I merge cells A1:C1 I finish up with the following:" "David" wrote: 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" wrote: 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 "Matthew Herbert" wrote: 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" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
populate cells within merge area
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 "David" wrote: 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 "Matthew Herbert" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select Merged Cells and Unmerge Spread Merge Data To All Cells | Excel Programming | |||
Location of Cell with Value in Merge Area | Excel Programming | |||
How to merge all selected areas into one area | Excel Programming | |||
how do populate empty cells with the contents of populated cells . | Excel Discussion (Misc queries) | |||
Auto populate cells based on 2 cells division. | Excel Discussion (Misc queries) |