Solved

# VBA EXCEL assign and categorize data

Posted on 2009-05-13
293 Views
Hi, another question: I have a sheet (is attached) where I need to categorize data according to the Rating. On sheet "Info" we can find the rating (column B) for the particular bond - on sheet "Risk Criteria" we can find the the Riskrating (column E) which indicates that A+ to A- will get the rating 3 (this inculdes also A as it is in between A+ and A-). Everything BB+ and below will get a 5 - if there is no rating it should also get a 5. The result should be displayed in sheet "Calculation". The CUSIP/ISIN is the ID. If you have any help how to solve this I would be very grateful.
Moreover, I would like to classify the country by assinig them another rating. This rating still has to be calculated according to the data i am downloading on sheet "Country". Here we can see that there are many bonds with a number (yield) - this data actually has to be sorted by the numer (yield) so that we can perform the assignment of the Risk Criteria. On sheet "Risk Criteria" we can the 20% having the highest number (yield) should get the number 5 (80.01% - 100%) this should include all the countries on the bottim of the table on sheet "Country". If there is not country found the Bond should get a 5 (as with the Rating). The result should be displayed on sheet "Calculation". Again, I am grateful for any help.
RatingCountry.xls
0
Question by:kbrasco
• 5
• 2

LVL 45

Expert Comment

0

LVL 45

Expert Comment

And other similar ratings?
AAA, AA, BBB, BB, BB+ and so on...
0

Author Comment

AAA = 1
AA+, AA, AA- = 2
A+, A, A- = 3
BBB+, BBB, BBB- = 4
all the rest = 5

Thanks
0

LVL 45

Expert Comment

kbrasco,
Hopefully the macro below takes care of the requirements of your paragraph 1. It's in the attached file. I'll be back later for the 2nd para. Let me know how you get on with the first macro.
Press the button on the Calculation sheet to get the results.
Patrick

Sub specialmacro()

Dim rng1 As Range

Dim celle1 As Range

Dim rng2 As Range

Dim celle2 As Range

With Sheets("Calculation")

Set rng1 = Range(.Cells(2, "B"), .Cells(.Cells.Rows.Count, "B").End(xlUp))

End With

With Sheets("Info")

Set rng2 = Range(.Cells(2, "B"), .Cells(.Cells.Rows.Count, "D").End(xlUp))

End With

For Each celle1 In rng1

celle1.Offset(0, 1) = WorksheetFunction.VLookup(celle1, rng2, 2, False)

celle1.Offset(0, 2) = WorksheetFunction.VLookup(celle1, rng2, 3, False)

Select Case UCase(celle1.Offset(0, 1))

Case Is = "AAA"

celle1.Offset(0, 3) = 1

Case Is = "AA", "-AA"

celle1.Offset(0, 3) = 2

Case Is = "A", "-A"

celle1.Offset(0, 3) = 3

Case Is = "BBB", "-BBB"

celle1.Offset(0, 3) = 4

Case Is = "D", "BB"

celle1.Offset(0, 3) = 5

Case Else

celle1.Offset(0, 3) = 5

End Select

Next celle1

End Sub
RatingCountry-01.xls
0

LVL 45

Accepted Solution

patrickab earned 500 total points
kbrasco,
I have corrected the ratings part. I'm not sure I've got it right yet - please check it out. It's in the attached file and in the 'Select Case' section of the VBA code below.
I will await your response before taking any further steps.
Patrick

Sub specialmacro()

Dim rng1 As Range

Dim celle1 As Range

Dim rng2 As Range

Dim celle2 As Range

With Sheets("Calculation")

Set rng1 = Range(.Cells(2, "B"), .Cells(.Cells.Rows.Count, "B").End(xlUp))

End With

With Sheets("Info")

Set rng2 = Range(.Cells(2, "B"), .Cells(.Cells.Rows.Count, "D").End(xlUp))

End With

For Each celle1 In rng1

celle1.Offset(0, 1) = WorksheetFunction.VLookup(celle1, rng2, 2, False)

celle1.Offset(0, 2) = WorksheetFunction.VLookup(celle1, rng2, 3, False)

Select Case UCase(celle1.Offset(0, 1))

Case Is = "AAA"

celle1.Offset(0, 3) = 1

Case Is = "AA", "AA-", "AA+"

celle1.Offset(0, 3) = 2

Case Is = "A", "A-", "A+"

celle1.Offset(0, 3) = 3

Case Is = "BBB", "BBB-", "BBB+"

celle1.Offset(0, 3) = 4

Case Is = "D", "BB", "BB-", "BB+"

celle1.Offset(0, 3) = 5

Case Else

celle1.Offset(0, 3) = 5

End Select

Next celle1

End Sub
RatingCountry-02.xls
0

Author Closing Comment

Excellent!
0

LVL 45

Expert Comment

kbrasco - Pleased it did what you wanted. Thanks for the grade - Patrick
0

## Featured Post

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.