excel - 动态消息框 - 无效的过程调用或参数
问题描述
我有两个宏分配给按钮,它们的功能基本相同。他们根据活动单元格(“属性名称”)从另一个工作表(“组合”)中的单元格中提取信息,然后将它们显示在消息框中。第二个宏在以“成熟度”开头的行上给了我一个运行时错误 5。
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
最终结果将显示带有 Maturity、Lender、Originator 和 Address 的消息框
解决方案
在这种情况下,不需要使用数组。
尝试这样的事情:
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
推荐阅读
- javascript - HTML5 视频:使用 JavaScript 在特定帧处停止
- java - JSP 上的错误 - 页面模板的语法错误
- java - 在java中获取键盘输入
- java - 执行 JAR 文件时的额外类路径目录
- java - 如何从 int[] 数组中返回正确的值?
- java - 如何在 Mac OS X 10.7.5 上运行 Eclipse Oxygen
- c# - 针对特定依赖项 NuGet
- java - 如何从Java中的Url中提取全名
- c++ - 有什么方法可以跳过 std::visit 中变体类型的一些重载组合?
- docker - Docker Push 在 Red Hat Server 中失败,出现错误“**denied: requested access to the resource is denied**”