• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Error: Run-time error '1004': Cannot change part of a merged cell.

C

craymer

New Member
I have used the macro recorder to write a simple macro to use the solver, advanced filter, etc. The macro worked perfect and then I added the advanced filter portion and now it is giving the following error:


Run-time error '1004': Cannot change part of a merged cell


I don't get this because this worksheet has no merged cell, particularly in the cells that pertain to this macro.

[pre]
Code: 
Sub Profit()
'
' Profit Macro
'This macro runs the solver to find the perfect markup percentage so every sale price cumulatively equals the contract value.
'
ActiveSheet.Unprotect Password:="pmcpassword"
SolverOk SetCell:="$M$3", MaxMinVal:=1, ValueOf:=0, ByChange:="$C$5", Engine:=1 _
, EngineDesc:="GRG Nonlinear"
SolverOk SetCell:="$M$3", MaxMinVal:=1, ValueOf:=0, ByChange:="$C$5", Engine:=1 _
, EngineDesc:="GRG Nonlinear"
SolverSolve UserFinish:=True
Range("E6:F21").Select
Selection.ClearContents
Range("B7").Select
Range("B6:C21").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"B2:C3"), CopyToRange:=Range("E6:F6"), Unique:=False
ActiveSheet.Protect Password:="pmcpassword", DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

When you debug, it doesn't like the following lines:


Range("B6:C21").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"B2:C3"), CopyToRange:=Range("E6:F6"), Unique:=False
[/pre]
I am not a VBA guru, but this is simply filling out the advanced filter dialog box.


Your help is appreciated!
 
Hui, that is the crazy thing. I have no merged cells in the entire worksheet that I know. I just received a macro to identify any merged cells on the worksheet. It works if I just manually run the advanced filter.
 
Are you able to upload the file

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Craymer


Your piece of code

[pre]
Code: 
Range("B6:C21").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"B2:C3"), CopyToRange:=Range("E6:F6"), Unique:=False
[/pre]
Works fine on a dummy set of data

Of course I don't know what values/criteria you are specifically using


So as mentioned above can you either upload the file or post the data in cells B2:C21 as well as E6:F21
 
Try running the below just in case some are merged that you don't know about

[pre]
Code: 
Sub UnmergeAllCells()

Dim ws As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False

For Each ws In Worksheets
ws.Cells.UnMerge
Next ws

Application.ScreenUpdating = True
Application.DisplayAlerts = True

MsgBox "Complete"

End Sub
[/pre]
 
Hi, craymer!

Consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.

Regards!
 
I tried running UnmergeAllCells() and I received the following error:


Run-time error '1004':

Application-defined or object-defined error


I have uploaded the workbook below. The issue lies on the Data Analysis tab where I am trying to advance filter. Another oddity, the zeros in the left table shouldn't be zeros. It is doing funny things.


https://www.dropbox.com/sh/20cc0yzkmdw6h3t/285CndCByx
 
I just had a thought, do any merged cells in the entire workbook impact a macro? I noticed that UnmergeAllCells() did unmerge some of the cells on other worksheets.
 
Craymer


I downloaded you file and tried the following

The Workbook opened on the SOV worksheet

I ran the Profit macro

It runs ok without error


Nothing is placed into E7:E30 !


Also the Headings in rows 5&6 are merged


So I


Unprotected the SOV Sheet

Unmerged rows 5&6

Copied heading from Row 5 to row 6

Rerun the Profit Macro

Now it works fine!


Are you using Excel or something else?

On a PC or Mac ?


Hui...
 
Hmmm...


E7:E30? The issue with the Macro lies in the Data Analysis worksheet. E7:F21 is supposed to be the results of the Advanced Filter from B7:B21. That is where I am getting the error in the Macro.


Do the merged cells in rows 5 and 6 impact the Macro if they are never referenced in the Macro?


I am using Excel 2010 on a Windows 7 computer.
 
You must log in or register to reply here.
Back
Top