Msgbox Question

Ekaye · Aug 12th 2016

I have the following, which returns a message box every time the code encounters the criteria. I would like to return only one message box at the conclusion of the sub which presents everything that meets the criteria. What's the best way to do this? Is there a way to have the program store those values that meet the criteria and return them at the end? Thanks in advance.

Code

Sub RollerCoaster()




Dim c As Range




Range("A2").Activate
Range(ActiveCell, ActiveCell.End(xlDown)).Select




For Each c In Selection
If c.Offset(0, 2).Text = "Wood" Then
MsgBox (c.Text)




End If
Next c








End Sub

Display More

**cytop** · Aug 12th 2016

Re: Msgbox Question

Code

Sub RollerCoaster() 
     
     
    Dim c As Range 
    Dim msg as String 
     
    Range("A2").Activate 
    Range(ActiveCell, ActiveCell.End(xlDown)).Select 
     
     
    For Each c In Selection 
        If c.Offset(0, 2).Text = "Wood" Then 
            msg = msg & c.Text & vbCrLf
        End If 
    Next c 


    If msg <> vbNullString Then       
        MsgBox msg
    End If


End Sub

Display More

but you may end up with a huge message box depending on how many items are found... That can be coded for but simple example above.

Ekaye · Aug 12th 2016

Re: Msgbox Question

Great, thank you. Can you please explain what the following line does? I assume this is what is storing the values. I tried the code without the "msg" after the equals sign, so it looked like this msg = c.Text & vbCrLf , but it only returned one value, so I am curious how this line works and what the significance of each element of it is. Thanks for your time.

msg = msg & c.Text & vbCrLf

**cytop** · Aug 12th 2016

Re: Msgbox Question

It stores the current content of the msg variable plus the contents of the cell with a new line appended each time it executes.

First time around msg is empty so just the cell contents and a new line get stored.
Next time, the cells contents and a new line are appended to the contents of msg and so on.

If it only reports 1 item then that's all that was found. Am assuming your comment "msg = c.Text & vbCrLf" was a typo as that will only store the last item found

Ekaye · Aug 12th 2016

Re: Msgbox Question

Got it, that makes sense. Thanks again.

Msgbox Question

Participate now!

Share