View Full Version : Need Help sorting Ratings with or without VBA
lokewolf
01-08-2009, 02:16 PM
Attached is a file I'm having trouble with. I need to figure out a way to sort criteria in 3 columns by formula or vba, since sorting ascending/descending doesn't work with these bond ratings, and I can't figure out a formula that works.
I've Added a (Triple A?) column to show what the formula or vba code should evaluate to.
If blanks are present in Columns B, C, or D...its ok.
For each range of cells (B thru D) in each row... If any other value other than "Aaa", or "AAA" are present, then it is NOT "triple A".I highlighted the rows in RED that appear to be ok, but really are not ok, because one of the values is not Aaa or AAA.
I'd greatly appreciate any help or advice!
Thanks,
ERik
georgiboy
01-08-2009, 02:56 PM
Here is what i came up with, may not be the best way but here it is.
Sub Checker()
Dim rCell As Range
Dim x As String
For Each rCell In Range("E2:E" & Range("A" & Rows.Count).End(xlUp).Row)
x = rCell.Offset(, -3).Value & rCell.Offset(, -2).Value & rCell.Offset(, -1).Value
If UCase(x) = "AAAAAAAAA" Then rCell.Value = "Yes"
If UCase(x) = "AAAAAA" Then rCell.Value = "Yes"
If UCase(x) = "AAA" Then rCell.Value = "Yes"
If rCell.Value = "" Then rCell.Value = "No"
Next
End Sub
Bob Phillips
01-08-2009, 03:09 PM
How about this formula
=IF(COUNTBLANK(B2:D2)+COUNTIF(B2:D2,"AAA")=3,"Yes","No")
slurpee55
01-08-2009, 03:26 PM
Or this - not as neat, but perhaps more apparent in how it works
=IF(AND(OR(B2="AAA",B2=""),(OR(C2="AAA",C2="")),OR(D2="AAA",D2="")),"YES","NO")
lokewolf
01-08-2009, 04:39 PM
Here is what i came up with, may not be the best way but here it is.
Sub Checker()
Dim rCell As Range
Dim x As String
For Each rCell In Range("E2:E" & Range("A" & Rows.Count).End(xlUp).Row)
x = rCell.Offset(, -3).Value & rCell.Offset(, -2).Value & rCell.Offset(, -1).Value
If UCase(x) = "AAAAAAAAA" Then rCell.Value = "Yes"
If UCase(x) = "AAAAAA" Then rCell.Value = "Yes"
If UCase(x) = "AAA" Then rCell.Value = "Yes"
If rCell.Value = "" Then rCell.Value = "No"
Next
End Sub
For the Code above, how do you get the "NO" to be "AUTO_SUB" instead? If I change the "YES" to "AUTO_AAA" that will be put on the sheet, but "NO" won't switch to "AUTO_SUB":dunno
All of the Formula answers work except if a row was blank. But I don't need to worry about it at this point.
Bob Phillips
01-08-2009, 04:43 PM
All of the Formula answers work except if a row was blank. But I don't need to worry about it at this point.
=IF(AND(COUNTBLANK(A2:D2)<>3,COUNTBLANK(B2:D2)+COUNTIF(B2:D2,"AAA")=3),"Yes","No")
lokewolf
01-08-2009, 04:50 PM
I should have been more specific...if the row is blank it puts "Yes", doesn't leave blank.
Bob Phillips
01-08-2009, 05:01 PM
=IF(COUNTBLANK(A2:D2)<>3,"",IF(COUNTBLANK(B2:D2)+COUNTIF(B2:D2,"AAA")=3,"Yes ","No"))
georgiboy
01-09-2009, 01:38 AM
Column E needed to be cleared each time you run the code because the code relys on the "No" to be an empty cell. I have now added the clear column to the code.
try this...
Sub Checker()
Dim rCell As Range
Dim x As String
Range("E:E").ClearContents
For Each rCell In Range("E2:E" & Range("A" & Rows.Count).End(xlUp).Row)
x = rCell.Offset(, -3).Value & rCell.Offset(, -2).Value & rCell.Offset(, -1).Value
If UCase(x) = "AAAAAAAAA" Then rCell.Value = "Yes"
If UCase(x) = "AAAAAA" Then rCell.Value = "Yes"
If UCase(x) = "AAA" Then rCell.Value = "Yes"
If rCell.Value = "" Then rCell.Value = "AUTO_SUB"
Next
End Sub
lokewolf
01-09-2009, 09:37 AM
That did the trick! Thanks to everyone who helped out!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.