| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 1004 - Cannot change part of a merged cell.
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Error 1004 - Cannot change part of a merged cell.
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" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Error 1004 - Cannot change part of a merged cell.
Sam - It's transferred from an Access module like..
As an example...the Name "V_Insert" as being Defined as Cells D33:D34. So if I unmerge Cells D33:D34, 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" wrote: 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" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Error 1004 - Cannot change part of a merged cell.
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" wrote: Sam - It's transferred from an Access module like.. As an example...the Name "V_Insert" as being Defined as Cells D33:D34. So if I unmerge Cells D33:D34, 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" wrote: 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" wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Error 1004 - Cannot change part of a merged cell.
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" wrote: 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" wrote: Sam - It's transferred from an Access module like.. As an example...the Name "V_Insert" as being Defined as Cells D33:D34. So if I unmerge Cells D33:D34, 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" wrote: 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" wrote: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Error 1004 - Cannot change part of a merged cell.
Hi,
You can use .value = "" An empty string is the usual trick. Sam "Bob Barnes" wrote: 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" wrote: 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" wrote: Sam - It's transferred from an Access module like.. As an example...the Name "V_Insert" as being Defined as Cells D33:D34. So if I unmerge Cells D33:D34, 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" wrote: 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" wrote: 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Error 1004 - Cannot change part of a merged cell.
Thank you - looks like all is OK.
"Sam Wilson" wrote: Hi, You can use .value = "" An empty string is the usual trick. Sam "Bob Barnes" wrote: 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" wrote: 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" wrote: Sam - It's transferred from an Access module like.. As an example...the Name "V_Insert" as being Defined as Cells D33:D34. So if I unmerge Cells D33:D34, 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" wrote: 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" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Runtime error '1004' Cannot change part of a merged cell | Excel Programming | |||
| "Cannot change part of a merged cell" when AutoFilter is turned on | Excel Programming | |||
| "Cannot Change Part of a Merged Cell" When updating link using mac | Excel Programming | |||
| Cannot change part of a merged cell | Excel Programming | |||
| ClearContent & 'Cannot change part of a merged cell' error | Excel Programming | |||
Linear Mode
