Error 1004 - Cannot change part of a merged cell.

[Bob Barnes]

I'm an Access Programmer running automation to Excel.

The Manager uses an Excel file that uses several "2 Merged Cells" to display
Values as Enlarged.

I should be able to redesign the Excel file, but it would be faster if I can
somehow
automatically transfer the data into the 2 merged cells?

Can this be done?

TIA - Bob

 

[Sam Wilson]

Hi,

How are you transferring data? If A1 & B1 are merged you can set the value
as simply as Range("A1").value = "Blah"

If you're pasting from a grid of data you'll need to do some extra work.

Sam

 

[Bob Barnes]

Sam - It's transferred from an Access module like..

As an example...the Name "V_Insert" as being Defined as Cells D3334.
So if I unmerge Cells D3334, and rename "V_Insert" as Cell D33, then
"remerge" (which I'll try after this is sent), it might work?

I'll Post again here..tahnk you.

Do Until .EOF
Select Case !ScrapCat
Case "Insert"
objXLSheet.Range("V_Insert").Clear: objXLSheet.Range("V_Insert") = !TC
Case "Raw"
objXLSheet.Range("V_Raw").Clear: objXLSheet.Range("V_Raw") = !TC
Case "Skin"
objXLSheet.Range("V_Skin").Clear: objXLSheet.Range("V_Skin") = !TC
Case "Painted"
objXLSheet.Range("V_Painted").Clear: objXLSheet.Range("V_Painted") = !TC
Case "Finished"
objXLSheet.Range("V_Finished").Clear: objXLSheet.Range("V_Finished") = !TC
End Select
.MoveNext
Loop

 

[Sam Wilson]

Hi,

it's the .clear that's giving you a problem, but you don't need to clear it
before assigning it a new value. Change:

objXLSheet.Range("V_Insert").Clear: objXLSheet.Range("V_Insert") = !TC

to just

objXLSheet.Range("V_Insert") = !TC

and you should be away.

Sam

 

[Bob Barnes]

Sam - You are Correct. Also, transferring to only one Cell (such
as..your..Range("A1").value = "Blah") works.

The reason I used "Clear" is if there's no data for the selected datespan .
How can I "erase, blank, delete" (not "Clear") those Cells where there is no
data?

I guess I could use Booleans as each value (with data is entered), and then
examine which Booleans weren't set to True...then set those values to what.."
", Null?

Thank you - Bob

 

[Sam Wilson]

Hi,

You can use .value = ""

An empty string is the usual trick.

Sam

 

[Bob Barnes]

Thank you - looks like all is OK.

Hi,

You can use .value = ""

An empty string is the usual trick.

Sam