CODEWITHSUNDEEP
excelvba
user36510
user36510

Reputation: 91

Intermediate memory for array entries

I try to pick out of a list some specific information and show it on a messagebox. I have succeeded in showing each one find in a separate messagebox, but I want to have them all in one, not in e.g. 10 separate ones. My code looks like this:

Sub notificator()

Worksheets("Sheet1").Calculate

Dim i As Integer, k As Integer, intValueToFind1 As String,
intValueToFind1 = "RUN"
For i = 1 To 150    
    If Cells(i, 7).Value = intValueToFind1 And Worksheets("Sheet1").Cells(i, 9).Value = Empty Then
    Beep
    MsgBox ("The pump " & Cells(i, 1).Value & " is in operation")
    End If
Next i

Is there a way (e.g. by using an array or an intermediate memory) to gather the information first (loop by loop) and at the end give it to the messagebox shown ?

Upvotes: 0

Views: 16

Answers (1)

SJR
SJR

Reputation: 23081

You can build up a string to show the results. Note that you should add sheet references throughout in case active sheet is not what you expect.

Sub notificator()

Worksheets("Sheet1").Calculate

Dim i As Long, k As Long, intValueToFind1 As String, s As String

intValueToFind1 = "RUN"

For i = 1 To 150
    If Cells(i, 7).Value = intValueToFind1 And Worksheets("Sheet1").Cells(i, 9).Value = Empty Then
        'Beep
        s = s & "," & Cells(i, 1).Value
    End If
Next i

MsgBox ("The pumps " & Mid(s, 2) & " are in operation")

End Sub

Upvotes: 1

Related Questions