I am currently trying to write a macro where i need to verify 2 conditions at the same time. I have try IF ..... and .... then, which doesn't work.... see code below.....
this test is included in two loops, in order to go through 2 different columns where i read out the values for two variables : client and commercial.
the test must verify that both the commercial and the client are in the same row on a month sheet.
thanks......
code :
Private Sub CommandButton1_Click()
Dim client As String
Dim mois As String
Dim commercial As String
Dim bool As Integer
Dim i As Integer 'compteur cellule
Dim k As Integer 'compteur client
Dim chiffre_aff As Variant
Dim temp As String
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Récupération du mois et du Commercial
bool = 0 'garanti au moins une exécution
i = 2
k = 3
chiffre_aff = 0
mois = ComboBox1().Value
commercial = ComboBox2().Value
Select Case mois
Case Is <> ""
'effacement de la colonne client
If commercial <> "" Then Sheets(commercial).Select
Range("A3:A150").ClearContents
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
'tri avec unicité des clients
Sheets(mois).Select
Range("N1:N150").ClearContents
Columns("C:C").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Columns( _
"N:N"), Unique:=True
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
'tri dans l'ordre alphabétique des clients
Columns("N:N").Select
Selection.Sort Key1:=Range("N2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
'export vers la feuille du commercial
Range("N2:N70").Select
Selection.ClearFormats
Selection.copy
Range("I3").Select
If commercial <> "" Then Sheets(commercial).Select
Range("A3").Select
ActiveSheet.Paste
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
'récup client
Sheets(commercial).Select
k = 4
Do While Cells(k, 1) <> ""
client = Cells(k, 1).Value
'Calcule du CA par client
Sheets(mois).Select
Do While Cells(i, 7).Value <> ""
'MsgBox ("ca marche")
'MsgBox (Cells(i, 3).Value)
'MsgBox (temp)
temp = Cells(i, 6).Value
'MsgBox (temp)
'If Cells(i, 7).Value = commercial Then
If Cells(i, 3).Value = client Then
'MsgBox (Cells(i, 3).Value)
'MsgBox (condition_ok)
chiffre_aff = chiffre_aff + temp
End If
'End If
i = i + 1
'Cells(k, 3).Value = chiffre_aff
Loop
Sheets(commercial).Select
Cells(k, 2).Value = chiffre_aff
k = k + 1
Loop
'''''''''''''''''''''''''''''''''''''''''''''''''''
bool = 1
End Select
Sheets(commercial).Select
MsgBox (chiffre_aff)
MsgBox (k)
MsgBox (i)
MsgBox (client)
'efface quand c'est fini
If bool = 1 Then Unload Me
''''''''''''''''''''''''''''''''''''''''''''''''
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub