Macro to show Message box in cells as well

[howard]

I have a macro to display a message box

I need to show each message in a seperate cell , but all the message displays on one cell


it would to show each message on a separate cell on sheet 'Macro"


It would be appreciated if someone could amend my code

 Sub Variance_Message()

Dim ws As Worksheet, r As Range, msg As String, ff As String
For Each ws In Sheets
    Set r = ws.Columns("b").Find("Variance")
    
    If Not r Is Nothing Then
        ff = r.Address
        Do
           If 5 < Abs(Val(CStr(r.Offset(0, 1).Value))) Then
               msg = msg & ws.Name & r.Address(0, 0)
            End If
            Set r = ws.Columns("b").FindNext(r)
        Loop Until ff = r.Address
    End If
   
Next
If Len(msg) = 0 Then
   MsgBox "No Variances Found"
   Exit Sub
Else
   MsgBox msg
   Worksheets("Macro").Range("a1:A15").Value = msg
End If


End Sub

 

[Fluff]

Maybe

Sub Variance_Message()

Dim ws As Worksheet, r As Range, msg As String, ff As String, Sp As Variant
For Each ws In Sheets
    Set r = ws.Columns("b").Find("Variance")
    
    If Not r Is Nothing Then
        ff = r.Address
        Do
           If 5 < Abs(Val(CStr(r.Offset(0, 1).Value))) Then
               msg = msg & ws.Name & r.Address(0, 0) & ","
            End If
            Set r = ws.Columns("b").FindNext(r)
        Loop Until ff = r.Address
    End If
   
Next
If Len(msg) = 0 Then
   MsgBox "No Variances Found"
   Exit Sub
Else
   MsgBox msg
   Sp = Split(msg, ",")
   Worksheets("Macro").Range("a1").Resize(UBound(Sp) + 1).Value = Sp
End If


End Sub

 

[howard]

Thanks for the help Fluff

the message box now shows each message separated by a comma for eg BR1 B128, BR2 b195,

this is perfect

The message must also be displayed from A1 onwards for eg

A1 Br1 B128
A2 Br2 B195 etc

See message box on Dropbox


kindly amend code accordingly

https://www.dropbox.com/s/d4dpohso8toj644/Message Box.PNG?dl=0

 

[Fluff]

In what way isn't it working?

 

[howard]

Hi Fluff


These are appearing in the col a as follows below

Book1
	A
1	BR1B128
2	BR1B128
3	BR1B128
4
Sheet1

Each item between the commas in the message box must be show in its own cell in Col A from row1 onwards

Br1 B128
Br2 B195 etc

 

[Fluff]

Got ya, try

Worksheets("Macro").Range("a1").Resize(UBound(Sp) + 1).Value = Application.Transpose(Sp)

 

[howard]

Hi Fluff


thanks very much. It now works 100%

 

[Fluff]

You're welcome & thanks for the feedback