Excel VBA Error 9 Sub Out of Range - Possibly need to fix Last Row/Col

Question

Thanks to all the help from our members, I'm really close to having this code nailed down. I have a large calendar used for pool planning, within the range of the cells they will have values depending on the schedule. I created an update button to press when all values need comments added to them. They are pulling data from a different sheet. All of the code works except for at the end. It is highlighting If arr(i, j) = cell.Value Then And not removing comments where cells no longer have values. I think the issue is with the last row/col call out but after looking through the forums I don't have the technical skills to use what I found to resolve my issue. Found this Error in finding last used cell in VBA - seems useful.

Private Sub Update_Click()

    Dim arr As Variant, element As Variant
    Dim i As Long, j As Long, listItems As Long, rwLast As Long, clLast As Long
    Dim comm As String
    Dim rng As Range, cell As Range

    listItems = Sheets("list").Range("A1").End(xlDown).Row
    rwLast = Cells.SpecialCells(xlCellTypeLastCell).Row ' Adjust to fit your needs
    clLast = Cells.SpecialCells(xlCellTypeLastCell).Column 'Idem

    Set rng = Sheets("list").Range("A1:A" & listItems)
    arr = Range("D11:CY148").Value

    With Worksheets("Finish Matrix")
        For i = 1 To rwLast - 10 'Adjust to make it more general, this is pretty rough
            For j = 1 To clLast - 3 'Idem
                If i = 3 Then
                End If
                comm = ""
                For Each cell In rng
                    If arr(i, j) = cell.Value Then
                        comm = comm & Chr(13) & cell.Offset(0, 1).Value
                    End If
                Next cell
                If Not (comm = "") Then
                    .Cells(10, 3).Offset(i, j).ClearComments
                    .Cells(10, 3).Offset(i, j).AddComment
                    .Cells(10, 3).Offset(i, j).Comment.Text Text:=comm
                End If
            Next j
        Next i
    End With

End Sub

Surely I am missing a minor adjustment in my code to get it squared away. I'd rather keep it a simple solution if possible. Thanks in advance.

The line clLast = Cells.SpecialCells(xlCellTypeLastCell).Column should be the problem. If you are setting the array as arr = Range("D11:CY148").Value, why not set clLast = 100 and then start the inner For loop as For j = 1 To UBound(arr, 2)? — Subodh Tiwari sktneer, Nov 27 '17 at 18:17
So I don't understand what you mean when you say `start the inner For loop as For J = to UBound(arr, 2)` I'm novice at programming and have no real understanding of VBA so don't know the vocabulary or reasoning. — Jamal Lewis, Nov 27 '17 at 20:50

score 0 · Answer 1 · answered Nov 27 '17 at 18:45

0

To find the Last Row and column, you can try this VBA UDF:

Function LastRowCol(Worksht As String) As Long()
Application.Volatile
    Dim WS As Worksheet, R As Range
    Dim LastRow As Long, LastCol As Long
    Dim L(1) As Long
Set WS = Worksheets(Worksht)
With WS
    Set R = .Cells.Find(what:="*", after:=.Cells(1, 1), _
                    LookIn:=xlValues, searchorder:=xlByRows, _
                    searchdirection:=xlPrevious)

    If Not R Is Nothing Then
        LastRow = R.Row
        LastCol = .Cells.Find(what:="*", after:=.Cells(1, 1), _
                    LookIn:=xlValues, searchorder:=xlByColumns, _
                    searchdirection:=xlPrevious).Column
    Else
        LastRow = 1
        LastCol = 1
    End If
End With

L(0) = LastRow
L(1) = LastCol
LastRowCol = L
End Function

You can then use something like the following in your code:

    Dim rwLast As Long, clLast As Long
    Dim wsName As String
    Dim rLcL As Variant

wsName = Worksheets("sheet1").Name 'change to desired worksheet

rLcL = lastrowcol(wsName)

rwLast = rLcL(0)
clLast = rLcL(1)

Debug.Print rwLast, clLast

answered Nov 27 '17 at 18:45

Ron Rosenfeld

53,870
7
28
60

So I don't understand the need for this lastrow/col part within VBA. Why do we do this? To clarify - I copy the first code into a module within the workbook. The second code I paste within the original code? – Jamal Lewis Nov 27 '17 at 19:50
@JamalLewis **You** wrote: *I think the issue is with the last row/col call out* so I provided a different, more robust method of determining the last row and column. And then I provided an example of how to use it within your macro. If determining the last row/col is not your issue, then I don't understand why you wrote what you did. – Ron Rosenfeld Nov 27 '17 at 20:55
Correct **I think**. This is the deepest my coding knowledge has been pushed, I don't actually know what I'm doing. I also thought adding "**I have no technical skills to use what I have found**" in my post would be helpful in getting a post from members that would clearly show me the issue in my code and maybe explain in a way I could understand and not have to deal with the same issue again. Frankly I personally don't care at all about this last row/col thing. I just want the code to refresh within my range given, either adjust/add/remove comments to cell with/without values. – Jamal Lewis Nov 27 '17 at 21:04

Excel VBA Error 9 Sub Out of Range - Possibly need to fix Last Row/Col

1 Answers1