vba Excel在msgbox中显示变量数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12792428/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel Display variable data in msgbox
提问by user1728174
There are two columns. one has some names of tables and second one has its values.
有两列。一个有一些表的名称，第二个有它的值。
Dim rng As Range
Set rng = [AH3:AH50] ' <-- adjust to your requirements
If Application.WorksheetFunction.Max(rng) > 95 Then
MsgBox "tables has sixe more than 95"
End If
Help required is:
1. Msgbox should also display the actual value of the cell it finds more than 95.
2. Also it should display the name of the corresponding table for which it is displaying the value in step one.
需要的帮助是：
1. Msgbox 还应该显示它找到的超过 95 的单元格的实际值。
2. 它还应该显示它在第一步中显示值的相应表的名称。
msgbox output should be:
"ABCD table has current size:96.6"
msgbox 输出应该是：
“ABCD 表的当前大小：96.6”
采纳答案by Jamie Bull
This code assumes the table names are in the column before the table values. Change the Offsetparameter from -1if that's not the case.
此代码假定表名在表值之前的列中。如果不是这种情况，请更改Offset参数-1。
Sub TestRange()
Dim rngValues As Range
Dim strTableName As String
Dim cell As Range
' Adjust ranges to suit
Set rngValues = [C2:C8]
For Each cell In rngValues
If cell.Value > 95 Then
MsgBox Range(cell.Address).Offset(0, -1).Value _
& " table has current size: " & cell.Value
End If
Next
End Sub
回答by chris neilsen
Try this
尝试这个
Updated to find all instances > threshold
更新以查找所有实例 > 阈值
Sub TestRange()
Dim rngValues As Range
Dim rngTables As Range
Dim mxVal As Variant
Dim idx As Long
' Adjust ranges to suit
Set rngValues = [F1:F10]
Set rngTables = [E1:E10]
mxVal = Application.WorksheetFunction.Max(rngValues)
Do While mxVal > 95
idx = Application.Match(mxVal, rngValues, 0)
MsgBox Application.Index(rngTables, idx) & " table has current size: " & mxVal
If idx < rngValues.Rows.count Then
Set rngValues = rngValues.Offset(idx, 0).Resize(rngValues.Rows.count - idx)
Set rngTables = rngTables.Offset(idx, 0).Resize(rngTables.Rows.count - idx)
mxVal = Application.WorksheetFunction.Max(rngValues)
Else
mxVal = 0
End If
Loop
End Sub
