Error using an Array in my coding

[zoog25]

Hello all,

I'm having an issue with a set of coding that contains an array. For whatever reason i'm getting the following error. Compile error: ByRef argument type mismatch. The error seems to be with the following line:
Call STNOCMultiple(rw, Ary(I)). Can someone please let me know what i'm doing wrong so i can fix it. Thank you.

Sub NOCDrafting(rw As Long)

Dim NOCws As Worksheet

Set NOCws = ThisWorkbook.Worksheets("NOC")

If NOCws.Cells(rw, "B") <> "Complete" Then
    Select Case NOCws.Cells(rw, "A").Value
        Case "PUB"
            If WorksheetFunction.CountA(NOCws.Cells(rw, "L").Resize(, 13)) = 13 Then
                If MsgBox("NOC Requirements have been meet. Would you like the NOC to be drafted?", vbQuestion + vbYesNo, "NOC Completed") = vbYes Then
                    Select Case NOCws.Cells(rw, "E").Value
                        Case "IMP AGR"
                            Select Case NOCws.Cells(rw, "I").Value
                                Case Empty, ""
                                    Call NOCSTD(rw)
                                Case Else
                                    Call NOCSTD(rw)
                                    Dim Ary As Variant: Ary = Split(NOCws.Cells(rw, "I"), ",")
                                    For I = 0 To UBound(Ary)
                                        Call STNOCMultiple(rw, Ary(I))
                                    Next I
                            End Select
                            NOCws.Cells(rw, "B").Value = "Complete"
                            MsgBox ("NOC has been Drafted, Please Review.")
                            'Call DraftingDate(rw)
                        Case "MAINT AGR"
                            Call MaintNOC(rw)
                            NOCws.Cells(rw, "B").Value = "Complete"
                            'Call DraftingDate(rw)
                        Case "PW AGR"
                            Call PWNOC(rw)
                            NOCws.Cells(rw, "B").Value = "Complete"
                            'Call DraftingDate(rw)
                        Case "ST PERMIT"
                            Call STNOC(rw)
                            NOCws.Cells(rw, "B").Value = "Complete"
                            'Call DraftingDate(rw)
                        Case Else
                            Call PWNOC(rw)
                            NOCws.Cells(rw, "B").Value = "Complete"
                            'Call DraftingDate(rw)
                    End Select
                Else
                    NOCws.Cells(rw, "B").Value = "Pending"
                End If
            End If
        Case "PVT"
            If WorksheetFunction.CountA(NOCws.Cells(rw, "L").Resize(, 16)) = 16 Then
                If MsgBox("NOC Requirements have been meet. Would you like the NOC to be drafted?", vbQuestion + vbYesNo, "NOC Completed") = vbYes Then
                    Call NOCPrivateDoc(rw)
                    NOCws.Cells(rw, "B").Value = "Complete"
                    'Call DraftingDate(rw)
                Else
                    NOCws.Cells(rw, "B").Value = "Pending"
                End If
            End If
    End Select
End If

End Sub

 

[DanteAmor]

STNOCMultiple

You can also put the code of that procedure.

 

[zoog25]

Here is the code for STNOCMultiple.

Sub STNOCMultiple(rw As Long, Ary As String)

'Disable other sheet Events
Application.EnableEvents = False

Dim Wordapp As Word.Application
    Dim Location As Variant
    Dim lrow As Long
    Dim NOCdate As Long
    Set NOCws = ThisWorkbook.Worksheets("NOC")
    Set STws = ThisWorkbook.Worksheets("Permits")
    Set CONws = ThisWorkbook.Worksheets("Contact")
    Set Wordapp = CreateObject("Word.Application")
    Dim PermitArray As String


    Dim wFile As String
    wFile = ActiveWorkbook.Path & "\NOC Templates\NOC Template.docx"
    If Dir(wFile) <> "" Then
        Wordapp.Documents.Open (wFile)
        Wordapp.Visible = True
    Else
        MsgBox "File does not exists"
        GoTo Finaled
    End If
    
'Find Permit Entry under Permit Log
Permit = PermitMultFD(Ary)

If Permit = Empty Then
    MsgBox ("Street Permit # is not found under Permit Log. Please verify that permit number is correct.")
    GoTo Finaled
End If

'Project Entry
Select Case STws.Cells(Permit, "F").Value
    Case Empty, ""
        If NOCws.Cells(rw, "F").Value < 10000 Then
            If NOCws.Cells(rw, "G").Value <> "" Then
                Wordapp.ActiveDocument.FormFields("TXProject").Result = "Street Permit # " & Ary & " for Tract " & NOCws.Cells(rw, "F").Value & " " & NOCws.Cells(rw, "G").Value & " " & NOCws.Cells(rw, "H").Value
            Else
                Wordapp.ActiveDocument.FormFields("TXProject").Result = "Street Permit # " & Ary & " for Tract " & NOCws.Cells(rw, "F").Value
            End If
        Else
            If NOCws.Cells(rw, "G").Value <> "" Then
                Wordapp.ActiveDocument.FormFields("TXProject").Result = "Street Permit # " & Ary & " for Parcel Map " & NOCws.Cells(rw, "F").Value & " " & NOCws.Cells(rw, "G").Value & " " & NOCws.Cells(rw, "H").Value
            Else
                Wordapp.ActiveDocument.FormFields("TXProject").Result = "Street Permit # " & Ary & " for Parcel Map " & NOCws.Cells(rw, "F").Value
            End If
        End If
    Case Else
            Dim STP As String: STP = STws.Cells(Permit, "E").Value
            Dim TP As String: TP = STws.Cells(Permit, "F").Value
            Dim X As Variant: X = Split(Replace(Join(Array(STP, TP), ","), " ", ""), ",")
            With CreateObject("System.Collections.ArrayList")
                For I = 0 To UBound(X)
                    .Add (X(I))
                Next I
                .Sort
                .Reverse
                PermitArray = Join(.toarray, " ,")
            End With
            If NOCws.Cells(rw, "F").Value < 10000 Then
                If NOCws.Cells(rw, "G").Value <> "" Then
                    Wordapp.ActiveDocument.FormFields("TXProject").Result = "Street Permit # " & PermitArray & " for Tract " & NOCws.Cells(rw, "F").Value & " " & NOCws.Cells(rw, "G").Value & " " & NOCws.Cells(rw, "H").Value
                Else
                    Wordapp.ActiveDocument.FormFields("TXProject").Result = "Street Permit # " & PermitArray & " for Tract " & NOCws.Cells(rw, "F").Value
                End If
            Else
                If NOCws.Cells(rw, "G").Value <> "" Then
                        Wordapp.ActiveDocument.FormFields("TXProject").Result = "Street Permit # " & PermitArray & " for Parcel Map " & NOCws.Cells(rw, "F").Value & " " & NOCws.Cells(rw, "G").Value & " " & NOCws.Cells(rw, "H").Value
                Else
                        Wordapp.ActiveDocument.FormFields("TXProject").Result = "Street Permit # " & PermitArray & " for Parcel Map " & NOCws.Cells(rw, "F").Value
                End If
            End If
End Select

'Location
Select Case STws.Cells(Permit, "L").Value
    Case "N/A", "", Empty
        Location = InputBox("Permit", "Tract / Parcel Location")
        Wordapp.ActiveDocument.FormFields("TXLocation").Result = Location
    Case Else
        Wordapp.ActiveDocument.FormFields("TXLocation").Result = "at " & STws.Cells(Permit, "L").Value
End Select

'Developer Information
Deve = STws.Cells(Permit, "O").Value
Wordapp.ActiveDocument.FormFields("TXDeveloper").Result = Deve

'Developer Address
Contact = NOCcontFD(Deve)

If Not ContactNoc = Empty Then
    Wordapp.ActiveDocument.FormFields("TXAddress").Result = CONws.Cells(Contact, "G").Value
    Wordapp.ActiveDocument.FormFields("TXCSZ").Result = CONws.Cells(Contact, "H").Value & ", " & CONws.Cells(Contact, "I").Value & " " & CONws.Cells(Contact, "J").Value
End If

'Agreement Number
If PermitArray = Empty Then
    Wordapp.ActiveDocument.FormFields("TXAgrSt").Result = "Street Permit # " & NOCws.Cells(rw, "I").Value
Else
    Wordapp.ActiveDocument.FormFields("TXAgrSt").Result = "Street Permit # " & PermitArray
End If

'Project Completion Date
Wordapp.ActiveDocument.FormFields("TXCompletionDate").Result = Format(NOCws.Cells(rw, "L").Value, "mmmm dd, yyyy")

Finaled:
Application.EnableEvents = True
End Sub

Sorry for the long coding. It's just a complex monster that is getting out of hand, but it's needed. As you can see the Ary holds a particular phrase/code that is used in the drafting of a document.

 

[DanteAmor]

Change to this:

Sub STNOCMultiple(rw As Long, Ary As Variant)

 

[zoog25]

Thanks DanteAmor.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.