I do realize that merged cells are bad news.

I'm stuck with an Excel 2003 worksheet, which contains this dreaded feature.
Users are complaining that on trying to paste text into merged cells gives
Excel Warnings:

"Data on the Clipboard is not the same size and shape as the selected
area. Do you want to paste anyway?"

And:

"Cannot change part of a merged cell."



Interestingly, though if the user hits F2, which places the cursor in the
formula bar, then the paste operation works!



I'm usually reasonably of Ok with VBA; but this one is stumping me.

Using VBA, I'm trying to (1) find a way of firstly trapping the Excel
Application warning(s) above and then, once trapped (2) placing the cursor
in the formula bar and finally (3) Paste the clipboard into the formula bar.



Has anyone any ideas please?



TIA



Phil

Does this could help you ?

Sub CopyToMergedCells()


Set Origin = Range("D1")
Set dest = Range("B1")

dest.Select 'To Select Merged Cells if it is the case

With Selection
If .MergeCells = False Then

Origin.Copy dest

Else

.ClearContents
.MergeCells = False

Origin.Copy dest

.MergeCells = True

End If

End With

End Sub

Mishell


"PPL" a écrit dans le message de news:
...
I do realize that merged cells are bad news.

I'm stuck with an Excel 2003 worksheet, which contains this dreaded
feature. Users are complaining that on trying to paste text into merged
cells gives Excel Warnings:

"Data on the Clipboard is not the same size and shape as the selected
area. Do you want to paste anyway?"

And:

"Cannot change part of a merged cell."



Interestingly, though if the user hits F2, which places the cursor in the
formula bar, then the paste operation works!



I'm usually reasonably of Ok with VBA; but this one is stumping me.

Using VBA, I'm trying to (1) find a way of firstly trapping the Excel
Application warning(s) above and then, once trapped (2) placing the cursor
in the formula bar and finally (3) Paste the clipboard into the formula
bar.



Has anyone any ideas please?



TIA



Phil

To UnMerge DestinationArea before Copy.


Sub UnMergeDestinationAreaAndPaste()

Dim rngToCheck As Range, rngCell As Range, rngChecked As Range

Set origin = Sheets("Sheet2").Range("G1:I3")
Set dest = Sheets("Sheet3").Range("B1")

Set rngToCheck = dest.Resize(origin.Rows.Count, origin.Columns.Count)

For Each rngCell In rngToCheck.Cells

If rngCell.MergeCells Then

rngCell.MergeCells = False

End If
Next rngCell

origin.Copy dest

End Sub

Mishell


"PPL" a écrit dans le message de news:
...
I do realize that merged cells are bad news.

I'm stuck with an Excel 2003 worksheet, which contains this dreaded
feature. Users are complaining that on trying to paste text into merged
cells gives Excel Warnings:

"Data on the Clipboard is not the same size and shape as the selected
area. Do you want to paste anyway?"

And:

"Cannot change part of a merged cell."



Interestingly, though if the user hits F2, which places the cursor in the
formula bar, then the paste operation works!



I'm usually reasonably of Ok with VBA; but this one is stumping me.

Using VBA, I'm trying to (1) find a way of firstly trapping the Excel
Application warning(s) above and then, once trapped (2) placing the cursor
in the formula bar and finally (3) Paste the clipboard into the formula
bar.



Has anyone any ideas please?



TIA



Phil

Thanks Mishell I'll try your ideas and I'll let you know
Do you have any ideas on how the Formula Bar can accessed directly from
VBA - that way I should be able to obviate messing with the merged cells?

Phil


"Mishell" wrote in message
...


To UnMerge DestinationArea before Copy.


Sub UnMergeDestinationAreaAndPaste()

Dim rngToCheck As Range, rngCell As Range, rngChecked As Range

Set origin = Sheets("Sheet2").Range("G1:I3")
Set dest = Sheets("Sheet3").Range("B1")

Set rngToCheck = dest.Resize(origin.Rows.Count, origin.Columns.Count)

For Each rngCell In rngToCheck.Cells

If rngCell.MergeCells Then

rngCell.MergeCells = False

End If
Next rngCell

origin.Copy dest

End Sub

Mishell


"PPL" a écrit dans le message de news:
...
I do realize that merged cells are bad news.

I'm stuck with an Excel 2003 worksheet, which contains this dreaded
feature. Users are complaining that on trying to paste text into merged
cells gives Excel Warnings:

"Data on the Clipboard is not the same size and shape as the selected
area. Do you want to paste anyway?"

And:

"Cannot change part of a merged cell."



Interestingly, though if the user hits F2, which places the cursor in the
formula bar, then the paste operation works!



I'm usually reasonably of Ok with VBA; but this one is stumping me.

Using VBA, I'm trying to (1) find a way of firstly trapping the Excel
Application warning(s) above and then, once trapped (2) placing the
cursor in the formula bar and finally (3) Paste the clipboard into the
formula bar.



Has anyone any ideas please?



TIA



Phil

"PPL" a écrit dans le message de news:
...
Thanks Mishell I'll try your ideas and I'll let you know
Do you have any ideas on how the Formula Bar can accessed directly from
VBA - that way I should be able to obviate messing with the merged cells?

You could try to put a STOP statement in your code.


Phil


"Mishell" wrote in message
...


To UnMerge DestinationArea before Copy.


Sub UnMergeDestinationAreaAndPaste()

Dim rngToCheck As Range, rngCell As Range, rngChecked As Range

Set origin = Sheets("Sheet2").Range("G1:I3")
Set dest = Sheets("Sheet3").Range("B1")

Set rngToCheck = dest.Resize(origin.Rows.Count, origin.Columns.Count)

For Each rngCell In rngToCheck.Cells

If rngCell.MergeCells Then

rngCell.MergeCells = False

End If
Next rngCell

origin.Copy dest

End Sub

Mishell


"PPL" a écrit dans le message de news:
...
I do realize that merged cells are bad news.

I'm stuck with an Excel 2003 worksheet, which contains this dreaded
feature. Users are complaining that on trying to paste text into merged
cells gives Excel Warnings:

"Data on the Clipboard is not the same size and shape as the selected
area. Do you want to paste anyway?"

And:

"Cannot change part of a merged cell."



Interestingly, though if the user hits F2, which places the cursor in
the formula bar, then the paste operation works!



I'm usually reasonably of Ok with VBA; but this one is stumping me.

Using VBA, I'm trying to (1) find a way of firstly trapping the Excel
Application warning(s) above and then, once trapped (2) placing the
cursor in the formula bar and finally (3) Paste the clipboard into the
formula bar.



Has anyone any ideas please?



TIA



Phil

Other than placing the project in Break mode - and displaying the VBA code -
the STOP statement seems to have no affect.
Am I missing something

"Mishell" wrote in message
...

"PPL" a écrit dans le message de news:
...
Thanks Mishell I'll try your ideas and I'll let you know
Do you have any ideas on how the Formula Bar can accessed directly from
VBA - that way I should be able to obviate messing with the merged cells?

You could try to put a STOP statement in your code.


Phil


"Mishell" wrote in message
...


To UnMerge DestinationArea before Copy.


Sub UnMergeDestinationAreaAndPaste()

Dim rngToCheck As Range, rngCell As Range, rngChecked As Range

Set origin = Sheets("Sheet2").Range("G1:I3")
Set dest = Sheets("Sheet3").Range("B1")

Set rngToCheck = dest.Resize(origin.Rows.Count, origin.Columns.Count)

For Each rngCell In rngToCheck.Cells

If rngCell.MergeCells Then

rngCell.MergeCells = False

End If
Next rngCell

origin.Copy dest

End Sub

Mishell


"PPL" a écrit dans le message de news:
...
I do realize that merged cells are bad news.

I'm stuck with an Excel 2003 worksheet, which contains this dreaded
feature. Users are complaining that on trying to paste text into merged
cells gives Excel Warnings:

"Data on the Clipboard is not the same size and shape as the selected
area. Do you want to paste anyway?"

And:

"Cannot change part of a merged cell."



Interestingly, though if the user hits F2, which places the cursor in
the formula bar, then the paste operation works!



I'm usually reasonably of Ok with VBA; but this one is stumping me.

Using VBA, I'm trying to (1) find a way of firstly trapping the Excel
Application warning(s) above and then, once trapped (2) placing the
cursor in the formula bar and finally (3) Paste the clipboard into the
formula bar.



Has anyone any ideas please?



TIA



Phil

I don't know how to access the Formula Bar directly from
VBA.

"PPL" a écrit dans le message de news:
...
Other than placing the project in Break mode - and displaying the VBA
code - the STOP statement seems to have no affect.
Am I missing something

"Mishell" wrote in message
...

"PPL" a écrit dans le message de news:
...
Thanks Mishell I'll try your ideas and I'll let you know
Do you have any ideas on how the Formula Bar can accessed directly from
VBA - that way I should be able to obviate messing with the merged
cells?

You could try to put a STOP statement in your code.


Phil


"Mishell" wrote in message
...


To UnMerge DestinationArea before Copy.


Sub UnMergeDestinationAreaAndPaste()

Dim rngToCheck As Range, rngCell As Range, rngChecked As Range

Set origin = Sheets("Sheet2").Range("G1:I3")
Set dest = Sheets("Sheet3").Range("B1")

Set rngToCheck = dest.Resize(origin.Rows.Count, origin.Columns.Count)

For Each rngCell In rngToCheck.Cells

If rngCell.MergeCells Then

rngCell.MergeCells = False

End If
Next rngCell

origin.Copy dest

End Sub

Mishell


"PPL" a écrit dans le message de news:
...
I do realize that merged cells are bad news.

I'm stuck with an Excel 2003 worksheet, which contains this dreaded
feature. Users are complaining that on trying to paste text into
merged cells gives Excel Warnings:

"Data on the Clipboard is not the same size and shape as the
selected area. Do you want to paste anyway?"

And:

"Cannot change part of a merged cell."



Interestingly, though if the user hits F2, which places the cursor in
the formula bar, then the paste operation works!



I'm usually reasonably of Ok with VBA; but this one is stumping me.

Using VBA, I'm trying to (1) find a way of firstly trapping the Excel
Application warning(s) above and then, once trapped (2) placing the
cursor in the formula bar and finally (3) Paste the clipboard into the
formula bar.



Has anyone any ideas please?



TIA



Phil

If you're just trying to assign values, you don't need to "type" the value into
the formula bar. You can use something like:

worksheets("Sheet1").range("A1").value = worksheets("sheet2").range("z99").value

or
worksheets("Sheet1").range("a1").mergearea.cells(1 ).value _
= worksheets("sheet2").range("z99").mergearea.cells( 1).value



PPL wrote:

Thanks Mishell I'll try your ideas and I'll let you know
Do you have any ideas on how the Formula Bar can accessed directly from
VBA - that way I should be able to obviate messing with the merged cells?

Phil

"Mishell" wrote in message
...


To UnMerge DestinationArea before Copy.


Sub UnMergeDestinationAreaAndPaste()

Dim rngToCheck As Range, rngCell As Range, rngChecked As Range

Set origin = Sheets("Sheet2").Range("G1:I3")
Set dest = Sheets("Sheet3").Range("B1")

Set rngToCheck = dest.Resize(origin.Rows.Count, origin.Columns.Count)

For Each rngCell In rngToCheck.Cells

If rngCell.MergeCells Then

rngCell.MergeCells = False

End If
Next rngCell

origin.Copy dest

End Sub

Mishell


"PPL" a écrit dans le message de news:
...
I do realize that merged cells are bad news.

I'm stuck with an Excel 2003 worksheet, which contains this dreaded
feature. Users are complaining that on trying to paste text into merged
cells gives Excel Warnings:

"Data on the Clipboard is not the same size and shape as the selected
area. Do you want to paste anyway?"

And:

"Cannot change part of a merged cell."



Interestingly, though if the user hits F2, which places the cursor in the
formula bar, then the paste operation works!



I'm usually reasonably of Ok with VBA; but this one is stumping me.

Using VBA, I'm trying to (1) find a way of firstly trapping the Excel
Application warning(s) above and then, once trapped (2) placing the
cursor in the formula bar and finally (3) Paste the clipboard into the
formula bar.



Has anyone any ideas please?



TIA



Phil





--

Dave Peterson