BENSON
05-03-2008, 10:48 PM
The two codes below work fine ,I am having trouble joining them to show two consecutive message boxes.I wish to join them into one piece of code,with the end result I get two message boxes appear onr after the other should a critera be met
Thanks for any help:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim msg As String
With ActiveSheet
LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 15 To 181
If i > 129 And i < 143 Then i = 143
If IsNumeric(.Cells(i, "J").Value) And IsNumeric(.Cells(i, "Q").Value) Then
If .Cells(i, "J").Value > .Cells(i, "Q").Value * 1.4 Then
msg = msg & .Cells(i, "A").Address & " - " & .Cells(i, "A").Value & vbNewLine
End If
End If
Next i
If msg <> "" Then
msg = "YOU MAY WANT TO CHECK THE BUILD TO ON THESE ITEMS" & vbNewLine & vbNewLine & msg
MsgBox msg
End If
End With
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim msg As String
With ActiveSheet
LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 15 To 181
If i > 129 And i < 143 Then i = 143
If IsNumeric(.Cells(i, "n").Value) And IsNumeric(.Cells(i, "x").Value) Then
If .Cells(i, "n").Value = .Cells(i, "x").Value * 1 Then
msg = msg & .Cells(i, "A").Address & " - " & .Cells(i, "A").Value & vbNewLine
End If
End If
Next i
If msg <> "" Then
msg = "THE ITEMS LISTED ARE NOT RECORDED ON STOCK SHEET" & vbNewLine & vbNewLine & msg
MsgBox msg
End If
End With
End Sub
Thanks for any help:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim msg As String
With ActiveSheet
LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 15 To 181
If i > 129 And i < 143 Then i = 143
If IsNumeric(.Cells(i, "J").Value) And IsNumeric(.Cells(i, "Q").Value) Then
If .Cells(i, "J").Value > .Cells(i, "Q").Value * 1.4 Then
msg = msg & .Cells(i, "A").Address & " - " & .Cells(i, "A").Value & vbNewLine
End If
End If
Next i
If msg <> "" Then
msg = "YOU MAY WANT TO CHECK THE BUILD TO ON THESE ITEMS" & vbNewLine & vbNewLine & msg
MsgBox msg
End If
End With
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim msg As String
With ActiveSheet
LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 15 To 181
If i > 129 And i < 143 Then i = 143
If IsNumeric(.Cells(i, "n").Value) And IsNumeric(.Cells(i, "x").Value) Then
If .Cells(i, "n").Value = .Cells(i, "x").Value * 1 Then
msg = msg & .Cells(i, "A").Address & " - " & .Cells(i, "A").Value & vbNewLine
End If
End If
Next i
If msg <> "" Then
msg = "THE ITEMS LISTED ARE NOT RECORDED ON STOCK SHEET" & vbNewLine & vbNewLine & msg
MsgBox msg
End If
End With
End Sub