宏范围/特定细胞msg框 [英] macro range / specific cells msg box
问题描述
现在我已经显示我错误看图像
Public Sub found(workheets)
Dim found As Boolean
Dim c As Range
found = False
对于每个c在工作表(data ).Range( D155,D456,D757,D1058,D1359,D1660,D1961:D1964,D36811,D36813,D38015,D38617,D39219,D39821,D40423,D41025,D52576,D53178,D54984,D55586,D56790,D57392, D58897)
如果c.Value = 8然后
found = True
c.Value = -1
结束如果
下一个
如果发现,那么MsgBox(ZAHLASTEBALENÍ!!!)& vbCrLf& (BALÍCÍMNOŽSTVÍJE 15 KS)
found = False
对于每个c在工作表(data)中。Range(D29,D31,D33,D35,D37,D39, D41,D43,D45,D47,D49,D51:D57,D59,D61,D63,D65,D67:D83,D85,D87,D89,D91:D95,D97:D101,D103,D105,D107,D109,D110: D111,D41944,D42246:D42250,D45263,D45265,D45267,D45269,D45271,D45273,D45275,D45277,D45279,D45280,D45581,D45882,D46183,D46484,D46785,D47086,D47387)
如果c.Value = 5然后
found = True
c.Value = -1
结束如果
下一个
如果找到则MsgBox(ZAHLASTEBALENÍ) &安培; vbCrLf& (BALÍCÍMNOŽSTVÍJE 6 KS)
found = False
对于每个c在工作表(data)中。范围(D3165,D3466,D3767,D4068,D4369,D4670, D4971,D5272,D5573,D5874,D6175:D10088,D10389,D10690,D41643,D41945,D42251,D42552,D42853,D43154,D43455,D43755,D44057,D44357,D44658,D44959,D48892,D49193,D49494,D49795,D50097, D50397,D50698,D50999,D51308:D51339)
如果c.Value = 8则
found = True
c.Value = -1
End If
Next
如果发现,那么MsgBox(ZAHLASTEBALENÍ)& vbCrLf& (BALÍCÍMNOŽSTVÍJE 9 KS)
End Sub
在下一个表单我调用调用Module1.check(工作表)
这部分代码显示错误。
除了应用上面的评论使用D155
而不是D155:D155
等,
以下是每次操作完成后只能显示msgbox一次的方法:
Dim as Boolean
found = false
对于每个c在工作表(data)。Range(...)
如果c.Value = 8然后
found = True
c.Value = -1
End If
Next
如果找到,那么MsgBox(ZAHLASTEBALENÍ!!!)& vbCrLf&(BALÍCÍMNOŽSTVÍJE 15 KS )
found = false
对于每个c在工作表(data)中。范围(...)
如果c.Value = 5然后
found =真
c.Value = -1
结束如果
下一个
如果找到,那么MsgBo x(ZAHLASTEBALENÍ)& vbCrLf& (BALÍCÍMNOŽSTVÍJE 6 KS)
found = false
对于每个c在工作表(data)。Range(...)
如果c.Value = 8然后
found = True
c.Value = -1
End If
Next
如果找到,那么MsgBox(ZAHLASTEBALENÍ)& vbCrLf& (BALÍCÍMNOŽSTVÍJE 9 KS)
编辑:下一个问题
如果提交的字符串的长度太长,超过255个字符,方法 Range
将失败。您可以通过将范围的规范分为两部分来解决这个问题:
Dim r As Range
Set r =工作表( 数据)范围(D29,D31,D33,D35,D37,D39,D41,D43,D45,D47,D49,D51:D57,D59,D61,D63,D65,D67:D83,D85, D87,D89,D91:D95,D97:D101,D103,D105,D107,D109,D110:D111,D41944,D42246:D42250,D45263,D45265,D45267,D45269,D45271,D45273)
Set r = Union(r,workheets(data)。Range(D45275,D45277,D45279,D45280,D45581,D45882,D46183,D46484,D46785,D47086,D47387))
对于r' - 从这里继续
can you help me with macro please? I have a 3 code which show me msg box only if - specific value. But dont work for me. :/ I dont known why. some errors, I would like to make all ranges and after show me msgbox when value be fulfill. Someone known?
NOW I HAVE BUT SHOW ME ERROR SEE IMAGE
Public Sub found(worksheets)
Dim found As Boolean
Dim c As Range
found = False
For Each c In worksheets("data").Range("D155,D456,D757,D1058,D1359,D1660,D1961:D1964,D36811,D36813,D38015,D38617,D39219,D39821,D40423,D41025,D52576,D53178,D54984,D55586,D56790,D57392,D58897")
If c.Value = 8 Then
found = True
c.Value = -1
End If
Next
If found Then MsgBox (" ZAHLASTE BALENÍ !!!") & vbCrLf & ("BALÍCÍ MNOŽSTVÍ JE 15 KS")
found = False
For Each c In worksheets("data").Range("D29,D31,D33,D35,D37,D39,D41,D43,D45,D47,D49,D51:D57,D59,D61,D63,D65,D67:D83,D85,D87,D89,D91:D95,D97:D101,D103,D105,D107,D109,D110:D111,D41944,D42246:D42250,D45263,D45265,D45267,D45269,D45271,D45273,D45275,D45277,D45279,D45280,D45581,D45882,D46183,D46484,D46785,D47086,D47387")
If c.Value = 5 Then
found = True
c.Value = -1
End If
Next
If found Then MsgBox (" ZAHLASTE BALENÍ") & vbCrLf & ("BALÍCÍ MNOŽSTVÍ JE 6 KS")
found = False
For Each c In worksheets("data").Range("D3165,D3466,D3767,D4068,D4369,D4670,D4971,D5272,D5573,D5874,D6175:D10088,D10389,D10690,D41643,D41945,D42251,D42552,D42853,D43154,D43455,D43755,D44057,D44357,D44658,D44959,D48892,D49193,D49494,D49795,D50097,D50397,D50698,D50999,D51308:D51339")
If c.Value = 8 Then
found = True
c.Value = -1
End If
Next
If found Then MsgBox (" ZAHLASTE BALENÍ") & vbCrLf & ("BALÍCÍ MNOŽSTVÍ JE 9 KS")
End Sub
In next sheet i call Call Module1.check(worksheets)
this part of code show me error.
In addition to applying the comments above (especially use "D155"
instead of "D155:D155"
, etc,
Here's how you can display the msgbox only once after the completeness of each operation:
Dim found as Boolean
found = false
For Each c In worksheets("data").Range(...)
If c.Value = 8 Then
found = True
c.Value = -1
End If
Next
If found then MsgBox (" ZAHLASTE BALENÍ !!!") & vbCrLf & ("BALÍCÍ MNOŽSTVÍ JE 15 KS")
found = false
For Each c In worksheets("data").Range(...)
If c.Value = 5 Then
found = True
c.Value = -1
End If
Next
If found then MsgBox (" ZAHLASTE BALENÍ") & vbCrLf & ("BALÍCÍ MNOŽSTVÍ JE 6 KS")
found = false
For Each c In worksheets("data").Range(...)
If c.Value = 8 Then
found = True
c.Value = -1
End If
Next
If found then MsgBox (" ZAHLASTE BALENÍ") & vbCrLf & ("BALÍCÍ MNOŽSTVÍ JE 9 KS")
EDIT: the next problem
The method Range
will fail if the length of the submitted string is too long, more than 255 characters. You can workaround it by splitting the specification of the range in two parts:
Dim r As Range
Set r = worksheets("data").Range("D29,D31,D33,D35,D37,D39,D41,D43,D45,D47,D49,D51:D57,D59,D61,D63,D65,D67:D83,D85,D87,D89,D91:D95,D97:D101,D103,D105,D107,D109,D110:D111,D41944,D42246:D42250,D45263,D45265,D45267,D45269,D45271,D45273")
Set r = Union(r, worksheets("data").Range("D45275,D45277,D45279,D45280,D45581,D45882,D46183,D46484,D46785,D47086,D47387"))
For Each d in r ' <-- Proceed from here
这篇关于宏范围/特定细胞msg框的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!