error 1004

[gronkette]

I keep getting this error when I run my macro....

"Cannot change part of a merged cell"

I've checked my sheets and I have no merged cells..... is this a bogus error or does it really mean something else?

 

[Jonmo1]

It could be the range you are changing is part of an Array..
I.E...
Where you highlighted A1:A10, then entered some formula and press CTRL + SHIFT + ENTER
Note, this is different from highlighting a single cell and entering a formula with CTRL + SHIFT + ENTER, then dragging down.
This is highlighting SEVERAL cells at once, entering a formula and pressing CSE..

 

[gronkette]

It works great when Size = "Large", but errors out on "Small".....that's what's so strang???


Sub DOG_SIZE()
Sheets("DATA ENTRY SHEET").Unprotect "test"
Dim sh As Variant
With Sheets("DATA ENTRY SHEET")
.AutoFilterMode = False
With .Range("A5:S47")
For Each sh In Array("Large", "Small")
.AutoFilter Field:=18, Criteria1:=sh
.Offset(1, 0).SpecialCells(xlVisible).Copy Sheets(sh).Range("K20")
Next sh
End With
.AutoFilterMode = False
End With
Sheets("DATA ENTRY SHEET").Protect "test"
End Sub

 

[gronkette]

Could this be because my data runs out and does not always fill the entire array? If so, how do I tell the array/macro to only use lines with data in them?

 

[gronkette]

Yes, when the array runs out of data the macro bombs out.... so, now I just need to know how to make the array variable in size - it changes monthly.... and I never know in advance how many lines of data I will have for each month?

thanks !