How to fix 'Application-defined or object-defined error' triggered by my cell range VBA

Question

Every time I try to run my macro, the first section surrounded by the LIRCounter runs just fine, but Excel returns a "Run-time error '1004': Application-defined or object-defined error" and the Debug highlights my Range(...).Merge sections on the rest of my macro.

I have tried reformatting the range by removing the .Merge, the Counter variable, and rewriting my entire code, but I can't narrow down what part of the range is causing the error.

Sub MergeCells()

    Set Worksheet = Worksheets("Technical Data")

    With Worksheet
        For LIRCounter = 44 To 15 Step -1
            If .Cells(LIRCounter, 19).Value = Not IsEmpty(Cells(LIRCounter, 19)) Then
            Else
                .Range(.Cells(LIRCounter, 21), .Cells(LIRCounter, 26)).Merge
            End If

            If .Cells(LIRCounter, 19).Value = Not IsEmpty(Cells(LIRCounter, 19)) Then
            Else
                .Range(.Cells(LIRCounter, 21), .Cells(LIRCounter, 26)) = "N/A"
            End If
        Next LIRCounter

        For ETCounter = 44 To 15 Step -1
            If .Cells(ETCounter, 3).Value = "Structural" Then
                .Range(.Cells(ETCounter, 4), .Cells(ETCounter, 12)).Merge
            End If

            If .Cells(ETCounter, 3).Value = "Structural" Then
                .Range(.Cells(ETCounter, 4), .Cells(ETCounter, 12)) = "N/A - Structural"
            End If
        Next ETCounter

        For ETCounter2 = 44 To 15 Step -1
            If .Cells(ETCounter2, 3).Value = "Structural" Then
                .Range(.Cells(ETCounter2, 15), .Cells(ETCounter2, 26)).Merge
            End If

            If .Cells(ETCounter2, 3).Value = "Structural" Then
                .Range(.Cells(ETCounter2, 15), .Cells(ETCounter2, 26)) = "N/A - Structural"
            End If
        Next ETCounter2
    End With
End Sub

The expected result is if "Structural" is selected from the dropdown menu in cell C15, then the cells D15:L15 merge into one cell, and the cells O15:Z15 merge into one cell, and both merged cells say "N/A - Structural". And the same goes for every row down to row 44. When the Macro is run, it just returns "Run-time error '1004': Application-defined or object-defined error" and no cells are merged.

How it is supposed to work

You have some structural issues going on in your code - `Not IsEmpty` is going to return a `Boolean`, and you're comparing that to a cell value - My guess is that evaluation will never return `True`, and you'll always be going into your `Else` there. — dwirony, Jan 22 '19 at 19:40
Yes, I couldn't get it to ever return true which is the only reason the else is there instead of just moving on to .Range(.Cells(LIRCounter, 21), .Cells(LIRCounter, 26)).Merge as a Then statement. Do you have any idea on how to fix it? — Josh Korthals, Jan 22 '19 at 19:42
That line should just be `If Not IsEmpty(Cells(LIRCounter, 19)) Then` — dwirony, Jan 22 '19 at 19:43
As for your `Merge` issue, are there any cells already merged on the page? If you're trying to merge columns with rows that are already merged, you may have some issues... — dwirony, Jan 22 '19 at 19:44
I followed your suggestion and it helped. Now half of my code works, but once it gets to the ETCounter2 section, the whole workbook freezes and crashes. Is that because of something wrong in my code or something else? — Josh Korthals, Jan 22 '19 at 19:51
I'm not sure what you're trying to do on your worksheet - can you post a screenshot? It looks like some of your merge areas are overlapping, which might through some errors. — dwirony, Jan 22 '19 at 20:17
The link is a screenshot of how it is supposed to work. It shows from column C to column Z (where my code should be affecting) — Josh Korthals, Jan 22 '19 at 20:27

Daniel Dušek · Accepted Answer · 2019-01-22T21:00:16.413

As a suggestion you could write the code simpler, remove a lot of redundant code. Just one for loop because all three loops are the same and just two if else. Why the error? It is not reproducible to me. Try to comment most of the code out and try to narrow down the source of the error. The source of the error could be the data in the sheet as well, so try to run the code in empty/dummy sheet first. HTH.

Option Explicit

Sub MergeCells()
    Dim TechnicalDataSheet As Worksheet
    Dim counter As Long

    Set TechnicalDataSheet = Worksheets("Technical Data")

    With TechnicalDataSheet 
        For counter = 44 To 15 Step -1
            If .Cells(counter, 19).Value = "" Then
                .Range(.Cells(counter, 21), .Cells(counter, 26)).Merge
                .Range(.Cells(counter, 21), .Cells(counter, 26)) = "N/A"
            End If

            If .Cells(counter, 3).Value = "Structural" Then
                .Range(.Cells(counter, 4), .Cells(counter, 12)).Merge
                .Range(.Cells(counter, 4), .Cells(counter, 12)) = "N/A - Structural"
                .Range(.Cells(counter, 15), .Cells(counter, 26)).Merge
                .Range(.Cells(counter, 15), .Cells(counter, 26)) = "N/A - Structural"
            End If
        Next counter
    End With
End Sub

This code does exactly what I have been looking for! I just have one question (sorry, I'm new to VBA). Is there a way to make this run in the background so I don't have to specifically run this code from the developer tab every time I want it to update the cells? — Josh Korthals, Jan 22 '19 at 22:03
Maybe you could use task scheduler? E.g. [here](https://stackoverflow.com/questions/22771185/how-to-set-recurring-schedule-for-xlsm-file-using-windows-task-scheduler). — Daniel Dušek, Jan 22 '19 at 22:50
Its all good, I figured it out. I just needed to use target columns to call the code. If i had just left it to activate on any change it slows down so much the workpack crashes. — Josh Korthals, Jan 23 '19 at 18:39

How to fix 'Application-defined or object-defined error' triggered by my cell range VBA

1 Answers1