Runtime error '13': Type mismatch

[IrishMist1748]

I have the following macro that builds a list of URLs in "column V" based upon a values in columns B through K and L through U. The problem is i am getting error: "Runtime error '13': Type mismatch referencing:

Cells(Rows.Count, DestCol).End(xlUp).Offset(1).Resize(lr).Value _
= Cells(1, myCols(c)).Resize(lr).Value

in the code below:

Sub Combine_Columns()
    Dim myCols()
    Dim c As Long, lr As Long
    Dim DestCol As String
    
    Application.ScreenUpdating = False
    myCols = Array("L", "M", "N", "O", "P", "Q", "R", "S", "T", "U")   '<-- Cols to combine
    DestCol = "V"                   '<-- Destination column
    For c = 0 To UBound(myCols)
        lr = Cells(Rows.Count, myCols(c)).End(xlUp).Row
        Cells(Rows.Count, DestCol).End(xlUp).Offset(1).Resize(lr).Value _
            = Cells(1, myCols(c)).Resize(lr).Value
    Next c
    Cells(1, DestCol).Delete Shift:=xlUp
    Application.ScreenUpdating = True
End Sub

Any ideas on what might be wrong? Any help is GREATLY appreciated!

 

[iliace]

Next time you get this error, click Debug. It will take you to the offending line - looks like you already did that, but just making sure.

In Break mode (while debugging), go to the Immediate window (Ctrl+G). On a new line in the Immediate window, type this:

?lr

Press Enter. What is the value? Does it make sense?
Next, type this.

?DestCol

Is it what you expected it to be? If not, is your algorithm for calculating it flawed, or did something else unexpected happen?
Next, type this.

?Cells(Rows.Count, DestCol).End(xlUp).Offset(1).Resize(lr)

Press Enter. What does it evaluate to?
Next, type this:

?c

Press Enter. What does c actually equal to at this point?

?Cells(1, myCols(c)).Resize(lr)

Press Enter. What does it evaluate to?

Obviously, if the value for lr doesn't make sense, go back and see how you got to that lr value. Same goes for the following three. Why didn't they give you the results you wanted?

There are many other debugging tactics that you might try. For example, what is this value here:

?Rows.Parent.Name

This will give you the name of the worksheet you're operating on. Is this the worksheet you expected? If not, how did you even get here? Should you be using more explicit object references to sheets, workbooks, ranges, etc?

You kinda have to do your own legwork here. Nobody else will do it for you. Except for a fee or a salary.

 

[IrishMist1748]

Thank you Iliace, this was some really good info of which I was not aware! I did find out what my problem was howerver. I found my problem to be I had to many many rows I was trying to process at a time. I found if I divide the number of rows to be divided up by half(processing half my rows then the other half) things work pretty well then. Perhaps the code is written in to clunky of a way causing to many iteations to complete the task with all of rows at once? Any suggestions on making the code more efficient would be appreciated.