View Full Version : Solved: Split test column as string
BENSON
03-18-2008, 11:20 PM
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
03-18-2008, 11:23 PM
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
03-19-2008, 01:59 AM
Do you meant test A against J against Q, and if so, what are the tests between A, J and Q?
BENSON
03-19-2008, 08:01 PM
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
03-19-2008, 10:39 PM
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
03-20-2008, 01:32 AM
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
03-20-2008, 10:53 PM
Thanks guys ,you the best.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.