Error Trying To Find Last Occupied Cell In A Range

[Ark68]

Dim lastrow as integer
With ws_master
      lastrow = .Cells(.Rows.Count, .Range("A12:A" & rng_svclwr)).End(xlUp).Row
End With

This is resulting in an error: "Type mismatch" with rng_svclwr = 26

I am trying to find the row of the last occupied cell in range A12:A26, which I expect a result of lastrow = 21 (A21 is the last cell in that range with a value in it)

 

[Joe4]

Can you show us where in your code "ws_master" and "rng_svclwr" are being set?

 

[Ark68]

Sure.

ws_master:

Public ws_master As Worksheet
Public ws_lists As Worksheet
Public ws_thold As Worksheet

Sub start1()
    'checked: 21-02-28
    cmn_path = "D:\WSOP 2020\"
    andata = cmn_path & "ActiveNet\"
    distpath = "D:\WSOP 2020\Distributables\"
    Set wb_tw = Workbooks("task allocation.xlsm")
    Set ws_master = wb_tw.Worksheets("Master")
    Set ws_lists = wb_tw.Worksheets("LISTS2")
    Set ws_thold = wb_tw.Worksheets("THold")
    clr_red = RGB(249, 93, 0)
    clr_green = RGB(0, 173, 2)
    clr_blue = RGB(103, 194, 203)
    clr_grey = RGB(166, 166, 166)
    Application.EnableEvents = True
    pda_reset '{Master_Set}
    'pda_fill
    
End Sub

rng_svclwr:

Sub bupdate_srvcpda(ByRef trigger_cancel As Boolean)
    'Stop
    Dim lastrow As Long
    
    Application.ScreenUpdating = False
    With ws_master
        .Activate
        mbevents = False
        .Unprotect
        srvcs_no = Application.WorksheetFunction.CountA(ws_thold.Range("AK1:AK8"))
        scol = 13 '14, 15, 16
        srccol = 1 '2-8
        srvc_drow = Application.WorksheetFunction.Match("ADD", .Columns(1)) + 1
      
        If trigger_cancel = False Then
            For L1 = 1 To srvcs_no
                If srvc_drow > 32 Then 'add row
                    srvc_drow = srvc_drow + 1
                    MsgBox "Not enough room. Row added at " & svc_lrow + 1, , "UNTESTED"
                    Stop
                    .Range("A" & srvc_drow & ":R" & srvc_drow).Insert Shift:=xlDown
                End If
                With .Range("H" & srvc_drow & ":Q" & srvc_drow)
                    .Cells.Value = ""
                    .Cells.Interior.Color = RGB(166, 166, 166)
                    .Cells.locked = True
                End With
                If L1 = 5 Then
                    scol = scol - 4
                End If
                Set rng_cpy = ws_master.Range("A" & srow & ":G" & srow)
                rng_cpy.Copy ws_master.Range("A" & srvc_drow)
                With .Cells(srvc_drow, scol)
                    .Value = ws_thold.Cells(srccol, 39)
                    .Interior.ColorIndex = 0
                End With
                If ws_thold.Cells(srccol, 36) = "RLN" Then
                    d1 = "Reline"
                Else
                    d1 = "Change"
                End If
                dmsg = d1 & " " & ws_thold.Cells(srccol, 37) & "-" & ws_thold.Cells(srccol, 38)
                With .Cells(srvc_drow, 2)
                    .Font.Size = 6
                    .Font.Color = vbBlack
                    .Font.Bold = True
                    .Value = dmsg
                    .HorizontalAlignment = xlCenter
                End With
                'added for pda service group sort
                With .Cells(srvc_drow, 18)
                    .Value = ws_thold.Cells(srccol, 43)
                    .Font.Size = 6
                    .Font.Color = RGB(229, 242, 251)
                End With
                .Rows(srvc_drow).AutoFit
                .Rows(srvc_drow).Cells.locked = True
                .Range(.Cells(srvc_drow, 1), .Cells(srvc_drow, 17)).VerticalAlignment = xlCenter
                scol = scol + 1
                srccol = srccol + 1
                srvc_drow = srvc_drow + 1
            Next L1
        Else 'cancel = true
            crid = ws_master.Cells(tmrow, 1)
            rng_svcupr = Application.WorksheetFunction.Match("ADD", .Columns(1)) + 1
            rng_svclwr = Application.WorksheetFunction.Match("Facility Maintenance Activities", .Columns(1)) - 3
            rta = 0
            For hcrus = rng_svclwr To rng_svcupr Step -1
                If .Cells(hcrus, 1) = crid Then 'eliminate pda services matching RID
                    .Range("A" & hcrus & ":Q" & hcrus).Delete Shift:=xlUp
                    rta = rta + 1
                End If
            Next hcrus
             
            'add empty rows to top up loss
            rng_svcupr = 12
            rng_svclwr = Application.WorksheetFunction.Match("Facility Maintenance Activities", .Columns(1)) - 2
            rta = rng_svclwr - rng_svcupr
            If rta < 21 Then 'top up needed
                lastrow = .Cells(.Rows.Count, .Range("A12:A" & rng_svclwr)).End(xlUp).Row
                rta = 21 - rta
                For L2 = 1 To rta
                    .Range("A" & lastrow & ":Q" & lastrow).Insert Shift:=xlDown
                Next L2
            End If
            'now add back the rows
            trigger_cancel = False
            bupdate_srvcpda trigger_cancel
        End If
        .Protect
        mbevents = True
    End With
    Application.ScreenUpdating = True
End Sub

 

[Joe4]

OK, I don't think your lastrow calculation makes much sense:

lastrow = .Cells(.Rows.Count, Range("A12:A" & .rng_svclwr)).End(xlUp).Row

You are using Cells, which has the format Cells(row, column)
Your rows argument in green looks fine.
But, the red part, which should be returning a column number or letter does not look right. It looks like you are returning a range.

Not sure what you are trying to do there, but I don't think it is correct.

 

[mikerickson]

The .Cells property is looking for either a number or a string for the column argument.

The OP code is giving it the array Range("A12:A" & rng_svclwr), which (if rng_svclwr is not 12) will not be interpreted as a string or as a letter.

If you are looking for the last occupied cell in the range A12:A26

lastRow = Range("A26").End(xlUp).Row

should do the trick after handling for "A26 has content" or "none of the cells has content" is added

 

[Ark68]

should do the trick after handling for "A26 has content" or "none of the cells has content" is added

Thanks Mike! I'm unsure how to to do this. I'm not sure what this means. The cell value (A26) is dynamic based on the value of rng_svclwr.
I'm testing with:

lastRow = Range("A" & rng_svclwr).End(xlUp).Row

and it appears to be working, but it that is wrong, I don't want to run into problems down the road.