Condition sur du texte

Le 23/06/2010 à 10:30

bonjour

ci dessous il y a un code mais j'ai une erreur a la ige 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

Le 23/06/2010 à 10:45

Bonsoir,

Pour un Range, autant tu peux lire la propriété "Text", autant tu ne peux pas la définir..

remplace tous tes Text par Value, et essaie....

pour faire rapidement, clique au milieu d'un "Text", puis Ctrl + H (Edition/remplacer)

tape Value, et dans la procédure en cours, à gauche..

Bonne soirée

Le 23/06/2010 à 10:53

merci beaucoup cousinhub

ça a marché

Rechercher des sujets similaires à "condition texte"