Message box to flag up negative figure

[Billdub417]

Hello,

Some help with a code please...

I have set up the below sheet showing quantities of different items.

There is a button (offscreen) that exports this list - I would like to add a message box that, when exporting this list, alerts for any negative figures in the period listed (i.e. in this case, period 9 = September, so the message box would say something like:

Error: Negative Values:
Pears: -1
Bananas: -4
Would you like to continue? Yes / NO

is this possible? VV Many thanks!

Sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3		31/01/2021	28/02/2021	31/03/2021	30/04/2021	31/05/2021	30/06/2021	31/07/2021	31/08/2021	30/09/2021	31/10/2021	30/11/2021	31/12/2021
4	Apples	0	2	-3	-4	5	-2	3	5	1	-5	-1	-3
5	Pears	6	1	4	6	-2	6	-1	2	-1	3	-3	4
6	Bananas	0	1	0	3	-2	-5	2	6	-4	1	2	6
7
8
9
10	Month	Period 9
Sheet1

 

[Norie]

Can you post your current code?

 

[Billdub417]

Sub Export()
Dim LResult As String
LResult = Trim(Right(Range("b10"), 2)) + 1

If Range(Cells(LResult, 4)) < 0 Then
   MsgBox Range("a4") & "=" & Range(LResult + 1, 4)
   End If

If Range(Cells(LResult, 5)) < 0 Then
    MsgBox Range("a5") & "=" & Range(LResult + 1, 5)
    End If

If Range(Cells(LResult, 6)) < 0 Then
    MsgBox Range("a6") & "=" & Range(LResult + 1, 5)
    End If

End Sub

thanks,

 

[Billdub417]

I have corrected it to this... which works, but brings up the errors one by one, how would you get these onto 1 message box?

Sub Export()

Dim LResult As String
LResult = Trim(Right(Range("b10"), 2))

If Range(Cells(4, LResult + 1), Cells(4, LResult + 1)) < 0 Then
   MsgBox Range("a4") & "=" & Cells(4, LResult + 1)
   End If
   
If Range(Cells(5, LResult + 1), Cells(5, LResult + 1)) < 0 Then
   MsgBox Range("a5") & "=" & Cells(5, LResult + 1)
   End If
   
If Range(Cells(6, LResult + 1), Cells(6, LResult + 1)) < 0 Then
   MsgBox Range("a6") & "=" & Cells(6, LResult + 1)
   End If

End Sub

 

[Norie]

Perhaps something like this.

Sub Export()
Dim rng As Range
Dim cl As Range
Dim LResult As String
Dim arrMessage As Variant
Dim cnt As Long
Dim Res As VbMsgBoxResult

    LResult = Trim(Right(Range("b10"), 2))

    ReDim arrMessage(1 To 3)
    
    Set rng = Cells(4, LResult + 1).Resize(3)
    
    For Each cl In rng.Cells
        If cl.Value < 0 Then
            cnt = cnt + 1
            arrMessage(cnt) = Range("A" & cl.Row).Value & "=" & cl.Value
        End If
    Next cl

    If cnt > 1 Then
        ReDim Preserve arrMessage(1 To cnt)
        
        Res = MsgBox(Join(arrMessage, vbCrLf) & vbCrLf & vbCrLf & "Would you like to continue?", _
                     vbYesNo, "Error: Negative Values")
        If Res = vbNo Then
            MsgBox "Export cancelled!", vbInformation, "Cancelled"
            Exit Sub
        End If
    End If

    ' ccde for export
    
End Sub

 

[Billdub417]

Perfect!!

 

[Billdub417]

Just one follow up point.... ive just noticed this only works for periods 5, 6, 9 & 11? would you know why ??

 

[Norie]

Oops, slight typo.

The If statement at the end should read like this.

    If cnt > 0 Then
        ReDim Preserve arrMessage(1 To cnt)
      
        Res = MsgBox(Join(arrMessage, vbCrLf) & vbCrLf & vbCrLf & "Would you like to continue?", _
                     vbYesNo, "Error: Negative Values")
        If Res = vbNo Then
            MsgBox "Export cancelled!", vbInformation, "Cancelled"
            Exit Sub
        End If
    End If