macro range / specific cells msg box

Question

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.

modify your `.Range("D155:D155", "D456:D456", "D757:D757", "D1058:D758",....)` to ``.Range("D155, D456, D757, D1058" ....)` . You need to make sure your `Range`s are valid. The `"` are in the begining and the end of the brackets `( )` — Shai Rado, Dec 15 '16 at 11:47
if your `worksheets` argument is actual `Worksheets` collection of `Workbook` object then you'd better pass your function a `workbook` object (i.e.: `Public Sub check(wb As Workbook)`) and then use `For Each c In wb.Worksheets("data")`, just not to mess up with built in objects names — user3598756, Dec 15 '16 at 11:57
ok i try to change, first part of code is good but second part show me error in this part: For Each c In worksheets("data").Range("D29,D31,D33,D35,D37,D39,D41,D43,D45,D47,D49,D51:D57,D59, — Fiínek Cahů, Dec 15 '16 at 12:20
please edit the question to post your updated code, and state precisely the error message. — A.S.H, Dec 15 '16 at 12:45

A.S.H · Accepted Answer · 2016-12-15T13:31:59.227

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

Thank you works perfect !! Now message box is on background and wait to click then show me on foreground how can I do msgbox only on foreground? [link] https://postimg.org/image/gf567vsnz/ — Fiínek Cahů, Dec 16 '16 at 08:50
You are welcome, glad it helped. For the new problem, I think it is of a completely different nature, so I suggest to ask a question on its own (Excel VBA msgBox pops in Background), hopefiully somebody has faced the same problem and will give you a solution. — A.S.H, Dec 16 '16 at 09:06

macro range / specific cells msg box

1 Answers1

Linked