PDA

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!