PDA

View Full Version : [SOLVED] Help Alter Code



BENSON
07-17-2008, 12:15 AM
The code below works ,it compares the value in collum "Q" against the corresponding values in collum "J" and shows a message box if the values in collum "J" exceed those in collum "Q" by 40%.I wish to alter the code so that if the value in collum"Q" is a blank or zero the code will not test the values

Thanks


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
Dim msg2 As String
With ActiveSheet
LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 15 To 200
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
If .Cells(i, "n").Value > .Cells(i, "x").Value Then
msg2 = msg2 & .Cells(i, "A").Address & " - " & .Cells(i, "A").Value & vbNewLine
End If
End If

Bob Phillips
07-17-2008, 01:18 AM
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
Dim msg2 As String
With ActiveSheet
LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 15 To 200
If i > 129 And i < 143 Then i = 143
If IsNumeric(.Cells(i, "J").Value) And IsNumeric(.Cells(i, "Q").Value) And .Cells(i, "Q").Value <> 0 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
If .Cells(i, "n").Value > .Cells(i, "x").Value Then
msg2 = msg2 & .Cells(i, "A").Address & " - " & .Cells(i, "A").Value & vbNewLine
End If
End If
Next i
End With
End Sub