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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.