Sujet Précédent Sujet Suivant

Condition sur du texte

Résolu
dianbobo Messages postés 151 Date d'inscription mardi 20 avril 2010 Statut Membre Dernière intervention 15 juin 2014 - 23 juin 2010 à 10:35
dianbobo Messages postés 151 Date d'inscription mardi 20 avril 2010 Statut Membre Dernière intervention 15 juin 2014 - 23 juin 2010 à 11:45
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


merci d'avance

1 réponse

Réponse 1 / 1
dianbobo Messages postés 151 Date d'inscription mardi 20 avril 2010 Statut Membre Dernière intervention 15 juin 2014
23 juin 2010 à 11:45
j'ai trouvé comment faire
il suffisait que je remplace 
.text
par 
.value
3

Discussions similaires

coder un Tchat comme roulettechat.fr/chat/texte.php
samote152 -
samote152 -

2 réponses
Texte aléatoire
sebchr -
rene38 -

5 réponses
Text Aleatoire
ideosphere -
BruNews -

3 réponses
souligner texte sous illustrator 10
cs_linsay -
linsay -

3 réponses
Zone de texte dans un canvas
_Moise_ -
_Moise_ -

4 réponses
Rejoignez-nous