PDA

View Full Version : Solved: Compare 2 collums and have a message box display varience



BENSON
03-16-2008, 11:34 PM
I wish to compare two collums of data "J4:J181" against "Q4:Q181" in most cases the cell values will be the same,or at least pretty close.However if a value in any of the cells in collum "J" exceeds 10% of the equilent cell value in collum "Q" a message box should appear before close listing, any such variences . The items that the data refers to is in collum "A4:A181" so if for example the value of cell "J4 " is greater by 10% of "Q4" the code i need would look at the item description in collum "A" in this case "ROLLS" and return a message such as

"Check Roll order"

THANKS

Bob Phillips
03-17-2008, 01:37 AM
Public Sub ProcessData()
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 = 4 To 181

If .Cells(i, "J").Value > .Cells(i, "Q").Value * 1.1 Then

msg = msg & .Cells(i, "A").Address & " - " & .Cells(i, "A").Value & vbNewLine
End If
Next i

If msg <> "" Then msg = "Check Roll Order" & vbNewLine & vbNewLine & msg
MsgBox msg

End With

End Sub

BENSON
03-17-2008, 09:26 PM
Thanks for the code Xld, I get a "Run TIme Error 13 ,Type Mismatch "as show below:

Public Sub ProcessData()
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 = 4 To 181

If .Cells(i, "J").Value > .Cells(i, "Q").Value * 1.1 Then

msg = msg & .Cells(i, "A").Address & " - " & .Cells(i, "A").Value & vbNewLine
End If
Next i

If msg <> "" Then msg = "Check Roll Order" & vbNewLine & vbNewLine & msg
MsgBox msg

End With

End Sub

Bob Phillips
03-18-2008, 02:05 AM
Must be text or errors. Try this variation



Public Sub ProcessData()
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 = 4 To 181

If IsNumeric(.Cells(i, "J").Value) And IsNumeric(.Cells(i, "Q").Value) Then

If .Cells(i, "J").Value > .Cells(i, "Q").Value * 1.1 Then

msg = msg & .Cells(i, "A").Address & " - " & .Cells(i, "A").Value & vbNewLine
End If
End If
Next i

If msg <> "" Then

msg = "Check Roll Order" & vbNewLine & vbNewLine & msg
MsgBox msg
End If
End With

End Sub

mdmackillop
03-18-2008, 06:38 AM
You could also use Conditional Formatting to highlight variances as the data is entered.

BENSON
03-18-2008, 10:33 PM
Perfect many thanks, it was a cell containing text that caused the previous error message