Solved: Split test column as string

[BENSON]

The code below works fine it compares to columns of data and returns a message indicating which rows do not meet the critera set, in this case a percentage varience between column "J" and "Q".At the moment the code looks at the entire columns .Is it possible to alter the code to look at cells in a specific range in the 3 columns "A,J,Q" . ie: row 4:129 and row 143:181

Thanks

[BENSON]

The code below works fine it compares to columns of data and returns a message indicating which rows do not meet the critera set, in this case a percentage varience between column "J" and "Q".At the moment the code looks at the entire columns .Is it possible to alter the code to look at cells in a specific range in the 3 columns "A,J,Q" . ie: row 4:129 and row 143:181

Thanks
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

[Bob Phillips]

Do you meant test A against J against Q, and if so, what are the tests between A, J and Q?

[BENSON]

Thanks for the reply xld ,the test is only against columns "J" & "Q" but I wish the code to only compare cells in ranges "J4:J129" "Q4:Q129" and range "J143:J181" "Q143:Q181" not the entire column

[mikerickson]

If highlighting the cell would work instead of a message box, you might set Conditional formatting on J4 to
=($J4>$Q4*1.1)
and copy that formatting to J4:128, Q4:Q128, J143:J181, Q143:Q181

Otherwise, splitting it into two loops should work

For i = 4 to 128
Rem existing block If statments
Next i
For i = 143 to 181
Rem existing block If statments
Next i

[Bob Phillips]

Do you mean this



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 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.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

[BENSON]

Thanks guys ,you the best.