VBA solution to paste text into megred cells

[PPL]

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

 

[Mishell]

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

 

[Mishell]

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]

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]

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.

 

[PPL]

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]

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

 

[Dave Peterson]

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