Dynamic Messagebox - Invalid Procedure Call or Argument

Question

I have two macros assigned to buttons that do basically the same thing. They draw info from cells in another worksheet ("Combined") based on the active cell ("Property Name") then display them in a message box. The second macro is giving me a runtime error 5 on the line beginning with "maturity".

Sub PropertyInfo()
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim ArrayRange As Variant
Dim ActCell As Variant

Set ActCell = ActiveCell
PropertyName = ActiveCell.Value

If IsEmpty(ActiveCell) = True Or IsNumeric(ActiveCell) = True Then
    MsgBox "Please select Property Name"    
Else
    Sheets("Combined").Select
    ArrayRange = Sheets("Combined").Range(Range("F1"), Range("F1").SpecialCells(xlLastCell))
    Sheets("Pivot").Select

    Maturity = Application.WorksheetFunction.VLookup(ActCell, ArrayRange, 36, False)
    Lender = Application.WorksheetFunction.VLookup(ActCell, ArrayRange, 41, False)
    Originator = Application.WorksheetFunction.VLookup(ActCell, ArrayRange, 42, False)
    Address = Application.WorksheetFunction.VLookup(ActCell, ArrayRange, 2, False)

    MsgBox "Property Name: " & PropertyName & vbCrLf & vbCrLf & "Loan Maturity: " & Maturity & vbCrLf & vbCrLf & "Lender: " & Lender & vbCrLf & vbCrLf & "Originator: " & Originator & vbCrLf & vbCrLf & "Property Address: " & Address & vbCrLf & vbCrLf & "Note: If dates are blank, the database doesnt have the info."

    Application.ScreenUpdating = True

End If

End Sub

end result will display the messagebox with Maturity, Lender, Originator, and Address

It's possible that vlookup isn't finding a match. What's the text of the error message? And what's different about this Sub from the one which works? — Tim Williams, Apr 18 '19 at 22:03
@Skin yes, definitely 36 columns. Even if i change 36 to a lower number, same result — carter, Apr 22 '19 at 14:49
@TimWilliams the only difference is that the data is pulled from different columns and named different things. The error is runtime error 5, invalid procedure call or argument — carter, Apr 22 '19 at 14:52
If you comment out the problem line does the next one run with no error? Where/how is Maturity declred? — Tim Williams, Apr 22 '19 at 14:58
@TimWilliams No, the next line has same issue. In this case "Lender" — carter, Apr 22 '19 at 14:59
Try adding `Debug.Print Sheets("Combined").Range(Range("F1"), Range("F1").SpecialCells(xlLastCell)).Address` is the output what you expect? — Tim Williams, Apr 22 '19 at 15:00
@TimWilliams I added that before the line where i was getting an error and am now getting an error on your code. Error 1004 application defined or object defined error — carter, Apr 22 '19 at 16:25
Add the line before you switch sheets - that's a whole other thing in your code which could be fixed - you should not rely on specific sheets being activated/selected or your code can become unreliable - see https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — Tim Williams, Apr 22 '19 at 16:31
@TimWilliams I got an error when adding it before switching as well.. I included "On error resume next" in front of the lines where i set maturity with vlookups and it removed the error message but the values are still blank. It looks like the code is not retrieving the vlookup correctly — carter, Apr 22 '19 at 21:53

Tim Williams · Answer 1 · 2019-04-23T16:30:25.060

1

There's no need to use an array in this case.

Try something like this instead:

Sub PropertyInfo()
    Dim lf2
    Dim PropertyName As Variant, m, shtCombined As Worksheet

    lf2 = vbLf & vbLf

    Set shtCombined = Sheets("Combined")
    PropertyName = ActiveCell.Value

    If Len(PropertyName) = 0 Or IsNumeric(PropertyName) = True Then
        MsgBox "Please select Property Name"
    Else
        'find the matching row number
        m = Application.Match(PropertyName, shtCombined.Range("F:F"), 0)

        If Not IsError(m) Then       '<< found a match?
            With shtCombined.Rows(m)
                '###adjust the column numbers below...###
                MsgBox "Property Name: " & PropertyName & lf2 & _
                "Loan Maturity: " & .Cells(41).Value & lf2 & _
                "Lender: " & .Cells(41).Value & lf2 & _
                "Originator: " & .Cells(41).Value & lf2 & _
                "Property Address: " & .Cells(41).Value & lf2 & _
                "Note: If dates are blank, the database doesnt have the info."
            End With
        Else
            MsgBox "No match found for '" & PropertyName & "'!"
        End If

    End If

End Sub

edited Apr 23 '19 at 16:30

answered Apr 22 '19 at 23:09

Tim Williams

154,628
8
97
125

Thanks Tim. If i use this code i get the "No Match Found" message no matter which property i have selected.. Could there be an issue with my data? My original code could not match the data either if i included the "on error resume next". I think the issue is related – carter Apr 23 '19 at 16:26
Just noticed there was a typo in my Match() - try it now. – Tim Williams Apr 23 '19 at 16:29
unfortunately the same issue... I've tried reformatting the text, using a separate module, nothing seems to work. The code, whether yours or the vlookup i used, wont return any values for the categories even though a match does exist in the data (i confirmed) – carter Apr 23 '19 at 19:29
Can you create a working Match formula on a worksheet? – Tim Williams Apr 23 '19 at 19:49

Dynamic Messagebox - Invalid Procedure Call or Argument

1 Answers1