Can't figure out why I am getting #REF error in cell

[mcomp72]

In my workbook, I have some cells that sometimes get copied from one place to another. Specifically, it is copying the formulas & values. Sometimes when this happens, I receive a #REF error in a cell that is adding up other cells in the same column, but I can't figure out why.

Here's a screenshot:

I selected "Trace error" and that is why you're seeing the arrows.

In Cell Q53, it has a simple formula: "=N53-L53". The rest of the rows down have the same (=N54-L54, =N55-L55, etc.).

In the formula bar, you can see the formula that is in the cell saying "#REF!"

Values and formulas are being copied into columns N-S right before "#REF!" appears. The code that does the copying is:

With ThisWorkbook.Sheets("Cost Summary")
    .Range(ColToCopyFrom & CS.SummaryLastRow - 6 & ":" & ColToCopyFrom & CS.SummaryLastRow).Copy
    .Range(ColToCopyTo & CS.SummaryLastRow - 6 & ":" & ColToCopyTo & CS.SummaryLastRow).PasteSpecial Paste:=xlPasteFormulas
End with

FYI: The ColToCopyFrom is not a merged cell. The ColToCopyTo is a merged cell. In this case, ColToCopyTo = "Q".

The code does paste the formulas and values in the proper cells, and I don't ever receive an error when the code runs, so I don't think there is anything wrong with the code. But after it runs is when the "#REF!" error shows up, so I'm guessing it has something to do with it.

The weird thing is, sometimes the above code works fine and doesn't cause the "#REF!" error. But under other conditions, it does. And sometimes, instead of saying "#REF!" it says "#VALUE!"

By the way, I know it's better not to have any merged cells, but I can't remove them because it would mess up the rest of the sheet. So I'm stuck with them.

Any thoughts on what is causing this error?

 

[Micron]

Can you actually calculate over merged cells like that? I thought not, but...
I don't see why you merged them anyway?

 

[mcomp72]

Can you actually calculate over merged cells like that? I thought not, but...
I don't see why you merged them anyway?

Interesting. I changed the formula in cell Q61 to only reference Column Q, and the error went away. What's odd is that it works sometimes, so I didn't realize you couldn't do it. Also, if I use the SUM button and select the range I want it add up, it puts the formula as "=SUM(Q45:S59)", which causes the error.

In any event, thanks for the help!

The reason I merged the cells is because further up on the sheet, the layout is such that I needed more cells on the sheet within the same area. The sheet gets printed, and it needs to follow a specific layout. If I could start over from scratch, I'd definitely try to find a way to avoid using merged cells.

 

[Alex Blakenburg]

I can't replicate your issue and have tried various combinations of merged cells.. To get a #REF! error in a Sum formula I have to have a #REF! error in one of the precedent cells.
In your image I can't see row 45. Do you have #REF! in row 45 ?

If not, are you able to post an XL2BB of your data ? Hopefull the merged cells come through in the XL2BB.

 

[RoryA]

I'd try unmerging the source cells in columns Q:S when the error occurs. It may be that you have an error in one of the merged cells but you just can't see it. (yet another reason merged cells are bad)

 

[mcomp72]

I can't replicate your issue and have tried various combinations of merged cells.. To get a #REF! error in a Sum formula I have to have a #REF! error in one of the precedent cells.
In your image I can't see row 45. Do you have #REF! in row 45 ?

If not, are you able to post an XL2BB of your data ? Hopefull the merged cells come through in the XL2BB.

There was not a #REF! error in row 45, but I did have a formula in it, and the formula appeared to be working fine. Still, I changed it it from "=SUM(Q32:S44)" to "=SUM(Q32:Q44)". I also changed the formula in Q61 from "=SUM(Q45:S59)" to "=SUM(Q45:Q59)". After that, the error went away and didn't come back. So, I guess it had to do with the SUM formula having the full range of the merged cells instead of just referencing the left-most column. I had no idea this could cause an issue.

Thanks all for the help!!

 

[RoryA]

I had no idea this could cause an issue

It can if one of the cells included in the merge has a REF error in it, even if you can't see it. It is also possible for there to be other values in the merged cells which will be included in the sum and you might never be aware that the total was actually wrong. Solution: don't merge cells.

 

[Micron]

You can replicate the look of merged cells with formatting, so further up, merge those if they must be and don't contain sums but format the rest. White fill with borders around the merged region should give you the same look.