Condition sur du texte

Dernière réponse : 23 Juin 2010 11:58 dans Programmation

dianbobo

23 Juin 2010 08:27:04

bonjour
ci dessous il y a un code mais j'ai une erreur a la lige 10:"erreur definie par l'application ou par l'objet " :??:

voila ce j'essai de faire :
en colonne E j'ai des texte et sur chaque ligne en colonne P je voudrai mettre les caracteres qui corespondent BBB,AAA......ect

Sub rating_note()

Dim k As Long, i As Long

Dim ws_f    As Worksheet

k = Worksheets("Feuil1").Cells(Rows.Count, 1).End(xlUp).Row

Set ws_f = Worksheets("Feuil1")

 
For i = 0 To k - 6

 
If ws_f.Cells(i + 6, 5).Text Like "*MICHELIN LUX SCS*" Then

    ws_f.Cells(i + 6, 16).Text = "BBB"

    ElseIf ws_f.Cells(i + 6, 5).Text Like "*SCHNEI*" Then

     ws_f.Cells(i + 6, 16).Text = " A-"

    ElseIf ws_f.Cells(i + 6, 5).Text Like "*WENDEL INV*" Then

     ws_f.Cells(i + 6, 16).Text = "BB"

    ElseIf ws_f.Cells(i + 6, 5).Text Like "*RENAULT*" Then

       ws_f.Cells(i + 6, 16).Text = "BB"

'UPM KYMMENE 6.125% 01/201

    ElseIf ws_f.Cells(i + 6, 5).Text Like "*REPSOL INT FIN*" Then

    ws_f.Cells(i + 6, 16).Text = " BBB"

    ElseIf ws_f.Cells(i + 6, 5).Text Like "*SODEXHO*" Then

     ws_f.Cells(i + 6, 16).Text = "BBB+"

    ElseIf ws_f.Cells(i + 6, 5).Text Like "*OTE PLC*" Then

     ws_f.Cells(i + 6, 16).Text = "BBB-"

    ElseIf ws_f.Cells(i + 6, 5).Text Like "*PUBLICIS*" Then

     ws_f.Cells(i + 6, 16).Text = "BBB+"

 
'SCHERING-PLOUGH 5.375% 01

ElseIf ws_f.Cells(i + 6, 5).Text Like "*TECHNIP SA *" Then

     ws_f.Cells(i + 6, 16).Text = "BBB+"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*BERTELSMANN AG *" Then

     ws_f.Cells(i + 6, 16).Text = "BBB"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*SUEDZUCKER*" Then

     ws_f.Cells(i + 6, 16).Text = "BBB"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*KRAFT FOODS INC*" Then

     ws_f.Cells(i + 6, 16).Text = "BBB"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*WOLTERS KLUWER*" Then

     ws_f.Cells(i + 6, 16).Text = "BBB+"

'TCHIBO HOLDING 4.50% 10/2

ElseIf ws_f.Cells(i + 6, 5).Text Like "*GROUP NYSE EURONEXT*" Then

     ws_f.Cells(i + 6, 16).Text = "AA-"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*ADECCO INT FIN SBV*" Then

     ws_f.Cells(i + 6, 16).Text = "BBB-"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*LAFARGE*" Then

     ws_f.Cells(i + 6, 16).Text = "BBB-"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*ATT INC*" Then

     ws_f.Cells(i + 6, 16).Text = "A"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*OAT*" Then

     ws_f.Cells(i + 6, 16).Text = "AAA"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*CEDULAS TDA*" Then

     ws_f.Cells(i + 6, 16).Text = "AAA"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*DEXIA MPAL AGENCY*" Then

     ws_f.Cells(i + 6, 16).Text = "AAA"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*BELGELEC FIN*" Then

     ws_f.Cells(i + 6, 16).Text = "A"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*CFF*" Then

     ws_f.Cells(i + 6, 16).Text = "AAA"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*LA POSTE*" Then

     ws_f.Cells(i + 6, 16).Text = "A"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*BAYER *" Then

     ws_f.Cells(i + 6, 16).Text = "A-"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*EIB *" Then

     ws_f.Cells(i + 6, 16).Text = "AAA"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*EDF*" Then

     ws_f.Cells(i + 6, 16).Text = "A+"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*GOVT OF SPAIN*" Then

     ws_f.Cells(i + 6, 16).Text = "AA+"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*BPCE*" Then

     ws_f.Cells(i + 6, 16).Text = "A+"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*GRECE*" Then

     ws_f.Cells(i + 6, 16).Text = "BBB+"

 
ElseIf ws_f.Cells(i + 6, 5).Text Like "*BEI*" Then

     ws_f.Cells(i + 6, 16).Text = "AAA"

 
'EUROP INVEST BK 4.625% 04

ElseIf ws_f.Cells(i + 6, 5).Text Like "*ITW FINANCE EUR*" Then

     ws_f.Cells(i + 6, 16).Text = "A+"

 
 
'STE SFR SA 3.375% 07/12

 
 
 
 
 
'AIR Products And CHEMICAL

'AIR Products And CHEMICAL

ElseIf ws_f.Cells(i + 6, 5).Text Like "*INVESTOR AB*" Then

     ws_f.Cells(i + 6, 16).Text = "AA-"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*CENTRICA PLC*" Then

     ws_f.Cells(i + 6, 16).Text = "A-"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*IBERDROLA*" Then

     ws_f.Cells(i + 6, 16).Text = "A-"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*LVMH*" Then

     ws_f.Cells(i + 6, 16).Text = "A-"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*SIEMENS*" Then

     ws_f.Cells(i + 6, 16).Text = "A+"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*SANDVIK AB*" Then

     ws_f.Cells(i + 6, 16).Text = "BBB"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*STATOILHYDRO ASA*" Then

     ws_f.Cells(i + 6, 16).Text = "AA-"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*IBM CORP*" Then

     ws_f.Cells(i + 6, 16).Text = "A+"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*AUCHAN*" Then

     ws_f.Cells(i + 6, 16).Text = "A"

 
ElseIf ws_f.Cells(i + 6, 5).Text Like "*DANAHER*" Then

     ws_f.Cells(i + 6, 16).Text = "BBB+"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*BTP ITALIE*" Then

     ws_f.Cells(i + 6, 16).Text = "A+"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*GE CAPITAL EUR FUNDING*" Then

     ws_f.Cells(i + 6, 16).Text = "AA+"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*ABERTIS INFRA*" Then

     ws_f.Cells(i + 6, 16).Text = "A-"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*SCHNEIDER*" Then

     ws_f.Cells(i + 6, 16).Text = "A-"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*DEUT TELEKOM*" Then

     ws_f.Cells(i + 6, 16).Text = "A"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*BOUYGUES*" Then

     ws_f.Cells(i + 6, 16).Text = "A-"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*PEUGEOT*" Then

     ws_f.Cells(i + 6, 16).Text = "BB+"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*FRANCE TELECOM*" Then

     ws_f.Cells(i + 6, 16).Text = "A-"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*MERCK FINANZ AG*" Then

     ws_f.Cells(i + 6, 16).Text = "BBB+"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*ASTRAZENECA PLC*" Then

     ws_f.Cells(i + 6, 16).Text = "AA-"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*BRISA*" Then

     ws_f.Cells(i + 6, 16).Text = "BBB"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*ATLANTIA SPA*" Then

     ws_f.Cells(i + 6, 16).Text = "A-"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*KONINKLIJKE DSM NV*" Then

     ws_f.Cells(i + 6, 16).Text = "A-"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*ANGLIAN WA*" Then

     ws_f.Cells(i + 6, 16).Text = "A-"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*RODAMCO EUR FIN BV*" Then

     ws_f.Cells(i + 6, 16).Text = "A"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*ATLAS COPCO AB*" Then

     ws_f.Cells(i + 6, 16).Text = "A-"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*VOLKSWAGEN*" Then

     ws_f.Cells(i + 6, 16).Text = "A-"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*GE CAP EUR FUND*" Then

     ws_f.Cells(i + 6, 16).Text = "AA+"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*BMW US CAPITAL LLC*" Then

     ws_f.Cells(i + 6, 16).Text = "A-"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*ROYAUME DE BELGIQUE*" Then

     ws_f.Cells(i + 6, 16).Text = "A+"

ElseIf ws_f.Cells(i + 6, 5).Text Like "*BANQUES POPULAIRES*" Then

     ws_f.Cells(i + 6, 16).Text = "AAA"

Else

    ws_f.Cells(i + 6, 16).Text = ""

End If

 
Next

End Sub