VBA debugging headache

Question

I have written this piece of code which analyses the size of imported data in a sheet. The goal is to identify which are the last row and column of the imported data, and then select and format that range as a table and assign it a name. Sounds simple, right?

When I run my code, I seem to get an error at the part where I define the range to be formatted. I use the variable lCol and lRow for the end of the range which stands for the last column and last row, as figured out in the code before. The error I get is:

Run-time error '1004':

Method '_Default' of object 'Range' failed

I've literally dug through every single forum post I could find on this matter, but I still haven't figured out what's wrong. I am also quite new to VBA so excuse me if it involves a very simple solution.

The entire code is:

Sub TextBox1_Click()

End Sub

Sub Range_Find_Method_Row()

    Worksheets("Copy Quick Report Here").Activate

'Finds the last non-blank row on a sheet/range.

Dim lRow As Long

    On Error Resume Next
    lRow = Cells.Find(What:="*", _
                    After:=Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row
    On Error GoTo 0


End Sub


Sub Range_Find_Method_Column()
'Finds the last non-blank row on a sheet/range.

Dim lCol As Long

    On Error Resume Next
    lCol = Cells.Find(What:="*", _
                    After:=Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByColumns, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Column
    On Error GoTo 0


End Sub

Sub Format_as_Table()


' Format_as_Table Macro
' This Macro formats the imported Quick Report as a table. This creates headers which are necessary for
' the summary calculations.

    Sheet1.Activate
    Sheet1.Range(Cells(5, 2), Cells(lRow, lCol)).Select     %%%%ERROR HERE%%%%%
    ActiveSheet.ListObjects.Add(xlSrcRange, Range(Cells(5, 2), Cells(lRow, lCol)), , xlYes).Name = "KPI_Table"
    Range("KPI_Table[#All]").Select
    ActiveSheet.ListObjects("KPI_Table").TableStyle = "TableStyleLight1"
    ActiveWindow.ScrollColumn = 1


End Sub

I would appreciate it enormously if someone can help me with this. Regards D.

Answer 1

Your problem is you've created 3 different sub, each independent of each other.
You need to understand the life cycle and scope of a variable.

Having said that, your variables lrow and lcol where immediately destroyed after the procedures Range_Find_Method_Row() and Range_Find_Method_Row() finished its execution.

It is not recognized on your third and final procedure Format_as_Table().

Now, how do we make it work. Well, the first answer is within the link I've posted. You can declare it as Module-Level or what most of us call Global Variable (My bad, Global variables are declared using Public key word and not Dim). That way, your variable persist event after the procedure you call it finishes execution. Something like this:

Option Explicit
Dim lcol As Long
Dim lrow As Long

Sub Range_Find_Method_Row()
    '~~> Notice that I did a little modification here, I'm not a fan of Activate Method
    Dim ws As Worksheet
    Set ws = Worksheets("Copy Quick Report Here")

    On Error Resume Next
    lRow = ws.Cells.Find(What:="*", _
                After:=ws.Range("A1"), _
                LookAt:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Row
    On Error GoTo 0
End Sub

---

Sub Range_Find_Method_Column()
    Dim ws As Worksheet
    Set ws = Worksheets("Copy Quick Report Here")

    On Error Resume Next
    lCol = ws.Cells.Find(What:="*", _
                After:=ws.Range("A1"), _
                LookAt:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Column
    On Error GoTo 0
End

---

Sub Format_as_Table()
    '~~> I'm not a fan of select/Activesheet as well, so re-tailored your code a bit
    With Sheet1
        .ListObjects.Add(xlSrcRange, Range(Cells(5, 2), _
            Cells(lRow, lCol)), , xlYes).Name = "KPI_Table"
        .ListObjects("KPI_Table").TableStyle = "TableStyleLight1"
    With
End Sub

The second (actually this should be the first) is to combine all your sub into one big procedure. I see no reason why you don't want to do that.

The third is if you're going to check the last row and column often in the rest of your code, you might want to use a function. Something like:

Function Range_Find_Last(ws As Worksheet, Optional SearchOrder As XlSearchOrder = xlByRows)
    Dim r As Range
    On Error Resume Next
    Set r = ws.Cells.Find(What:="*", _
                After:=ws.[A1], _
                LookAt:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=SearchOrder, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False)
    On Error GoTo 0

    If Not r Is Nothing Then
        If SearchOrder = xlByRows Then
            Range_Find_Last = r.Row
        Else
            Range_Find_Last = r.Column
        End If
    Else
        Range_Find_Last = "#Null!"
    End If
End Function

You can then use this function anywhere in your procedure like this:

Sub Format_as_Table()
    Dim lcol As Long, lrow As Long
    If Range_Find_Last(Sheet1) <> "#Null!" Then
        lrow = Range_Find_Last(Sheet1, xlByRows)
        lcol = Range_Find_Last(Sheet1, xlByColumns)
        '~~> Take note, we use Excels Default SearchOrder 
        '~~> to determine if we want to return row or column number
    End If 

    With Sheet1
        .ListObjects.Add(xlSrcRange, Range(Cells(5, 2), _
            Cells(lRow, lCol)), , xlYes).Name = "KPI_Table"
        .ListObjects("KPI_Table").TableStyle = "TableStyleLight1"
    With
End Sub

There are already a lot of variations of this kind of function and/or ways to find the last rows and columns. It is up to you to discover more.