Invalid procedure call or argument error

Question

I get the about error in the following sub. I narrow it down to the highlighted code line below; any ideas what I did wrong? :

Option Explicit

Dim CDws As Worksheet, PEws As Worksheet, PPws As Worksheet, PSws As Worksheet

Dim projCol As String, totalCol As String, faLocCol As String, projStsCol As String

Dim rnglookup As Range

Dim cdlRow As Long, pelRow As Long, pplRow As Long, pslRow As Long

Dim cdlCol As Long, pelCol As Long, pplCol As Long, pslCol As Long

Sub validate_PE_Data()

Dim projSts_UnApprvCnt As Long, projSts_CancelledCnt As Long

Dim c As Range, cdTblRange As Range, psTblRange As Range

Dim S As Variant, L As Variant

Set CDws = Worksheets("CASPR Milestone Dates")

Set PPws = Worksheets("PTN_PO List")

Set PEws = Worksheets("Port Excel")

Set PSws = Worksheets("Project Status")

'--------------------------------------------------------------------------------

    With CDws

        .Select

        cdlRow = .Cells(Rows.Count, "A").End(xlUp).Row

        cdlCol = .Cells(1, Columns.Count).End(xlToLeft).Column

        projStsCol = findcolumn("proj_status", CDws, 1, True)

        Set rnglookup = .Range(projStsCol & "2:" & projStsCol & cdlRow)

        projSts_CancelledCnt = Application.CountIf(rnglookup, "Cancelled")

        If projSts_CancelledCnt > 0 Then Set cdTblRange = .Range(Cells(1, 1), Cells(cdlRow, cdlCol))

    End With

    '---------------------------------------------------------------------------

    With PSws

        .Select

        pslRow = .Cells(Rows.Count, "A").End(xlUp).Row

        pslCol = .Cells(1, Columns.Count).End(xlToLeft).Column

        projStsCol = findcolumn("Project Status", PSws, 1, True)

        Set rnglookup = .Range(projStsCol & "2:" & projStsCol & pslRow)

        projSts_UnApprvCnt = Application.CountIf(rnglookup, "<>Closed")

        If projSts_UnApprvCnt > 0 Then Set psTblRange = .Range(Cells(1, 1), Cells(pslRow, pslCol))

    End With

    '---------------------------------------------------------------------------

    With PEws

        .Select

        pelRow = .Cells(Rows.Count, "A").End(xlUp).Row

        pelCol = .Cells(1, Columns.Count).End(xlToLeft).Column

        faLocCol = findcolumn("FALocation", PEws, 1, True)

        projCol = findcolumn("CASPRProjectNo", PEws, 1, True)

        totalCol = findcolumn("Total", PEws, 1, True)

        '=======================================================================

        '----   DELETE ROWS WHERE THE TOTAL AMOUNT IS ZERO

        '-----------------------------------------------------------------------

        Set rnglookup = .Range(totalCol & "$2:" & totalCol & "$" & pelRow)

        For Each c In rnglookup.Cells

            If c = 0 Then c.EntireRow.Delete

        Next c

        '=======================================================================

        '----   CHECK EACH PROJECT NUMBER IN THE PORT EXCEL TAB AGAINST THE

        '----   CASPR TAB AND THE PROJECT STATUSTAB. IF THE PROJECT IS CANCELLED

        '----   OR IS NOT APPROVED, FLAG THE ROW.

        '-----------------------------------------------------------------------

        Set rnglookup = .Range(projCol & "$2:" & projCol & "$" & pelRow)

        If projSts_CancelledCnt > 0 Or projSts_UnApprvCnt > 0 Then

            For Each c In rnglookup.Cells

                S = Application.VLookup(c.Value, cdTblRange, 2, False)                L = Application.VLookup(c.Value, psTblRange, 2, False)

                If S = "Cancelled" Or L <> "Approved" Then

                    With .Range(Cells(c.Row, 1), Cells(c.Row, pelCol)).Cells

                        .Interior.ColorIndex = 3

                        .Interior.Pattern = xlSolid

                        .Interior.PatternColorIndex = xlAutomatic

                    End With

                    c.Font.ColorIndex = 2

                End If

            Next c

        End If

    End With

    MsgBox "Here"

End Sub

Answer 1

Sorry, I missed that.

If I were troubleshooting, I would add a few debug.print lines right above the line giving you trouble (or add a watch).

Verify that c.value is not an error and is the value you expect

cdTblRange.address to make sure that it is set as you expect (which only happens if ProJStst_CancelledCnt is greater than zero), and that it has at least two columns

Beyond any issues that show up there, I'll defer to other members of the forum who might see something I don't.

Answer 2

cdTblRange is set in the highlighted line

With CDws

        .Select

        cdlRow = .Cells(Rows.Count, "A").End(xlUp).Row

        cdlCol = .Cells(1, Columns.Count).End(xlToLeft).Column

        projStsCol = findcolumn("proj_status", CDws, 1, True)

        Set rnglookup = .Range(projStsCol & "2:" & projStsCol & cdlRow)

        projSts_CancelledCnt = Application.CountIf(rnglookup, "Cancelled")

        If projSts_CancelledCnt > 0 Then Set cdTblRange = .Range(Cells(1, 1), Cells(cdlRow, cdlCol))    End With

    '---------------------------------------------------------------------------

    With PSws

        .Select

        pslRow = .Cells(Rows.Count, "A").End(xlUp).Row

        pslCol = .Cells(1, Columns.Count).End(xlToLeft).Column

        projStsCol = findcolumn("Project Status", PSws, 1, True)

        Set rnglookup = .Range(projStsCol & "2:" & projStsCol & pslRow)

        projSts_UnApprvCnt = Application.CountIf(rnglookup, "<>Closed")

        If projSts_UnApprvCnt > 0 Then Set psTblRange = .Range(Cells(1, 1), Cells(pslRow, pslCol))

    End With

    '---------------------------------------------------------------------------

    With PEws

        .Select

        pelRow = .Cells(Rows.Count, "A").End(xlUp).Row

        pelCol = .Cells(1, Columns.Count).End(xlToLeft).Column

        faLocCol = findcolumn("FALocation", PEws, 1, True)

        projCol = findcolumn("CASPRProjectNo", PEws, 1, True)

        totalCol = findcolumn("Total", PEws, 1, True)

        '=======================================================================

        '----   DELETE ROWS WHERE THE TOTAL AMOUNT IS ZERO

        '-----------------------------------------------------------------------

        Set rnglookup = .Range(totalCol & "$2:" & totalCol & "$" & pelRow)

        For Each c In rnglookup.Cells

            If c = 0 Then c.EntireRow.Delete

        Next c

        '=======================================================================

        '----   CHECK EACH PROJECT NUMBER IN THE PORT EXCEL TAB AGAINST THE

        '----   CASPR TAB AND THE PROJECT STATUSTAB. IF THE PROJECT IS CANCELLED

        '----   OR IS NOT APPROVED, FLAG THE ROW.

        '-----------------------------------------------------------------------

        Set rnglookup = .Range(projCol & "$2:" & projCol & "$" & pelRow)

        If projSts_CancelledCnt > 0 Or projSts_UnApprvCnt > 0 Then

            For Each c In rnglookup.Cells

                S = Application.VLookup(c.Value, cdTblRange, 2, False)                L = Application.VLookup(c.Value, psTblRange, 2, False)

                If S = "Cancelled" Or L <> "Approved" Then

                    With .Range(Cells(c.Row, 1), Cells(c.Row, pelCol)).Cells

                        .Interior.ColorIndex = 3

                        .Interior.Pattern = xlSolid

                        .Interior.PatternColorIndex = xlAutomatic

                    End With

                    c.Font.ColorIndex = 2

                End If

            Next c

        End If

    End With

    MsgBox "Here"

End Sub

Answer 3

That would suggest that one of your values is not to Excel's liking.

In the code provided, you do not seem to set cdTblRange. Is that being set in other code you aren't sharing?

Also, if c.range is an error value, that might cause problems as well- you might want to trap potential error values in your code, if there is any risk of unacceptable values showing up in your data grid.

Answer 4

I have tried worksheetfunction and I still got the same error message.

Answer 5

vlookup is a worksheet formula, not a VBA command. To access it;

S = Excel.worksheetfunction.vlookup

or

S = Application.worksheetfunction.vlookup

HTH