sk8bcn
Joined: Apr 13, 2004
|
  Posted:
Apr 04, 2006 - 10:13 |
|
I have problems to create a code for Excel VBA
Code: | Sub main()
Dim i As Integer
Dim NbLignesPLDG As Integer
Set NbLignesPLDG = Worksheets("PLDG").UsedRanges.Rows.Count
For i = 2 To NbLignePLDG
Dim j As Integer
Dim NBinfosclient As Integer
Dim occurence As Integer
Dim ligne As Integer
occurence = 0
NBinfosclient = Worksheets("infos client").UsedRanges.Rows.Count
For j = 2 To NBinfosclient
If Worksheets("PLDG")(i, 1) = Worksheets("infos client")(j, 1) Then
If occurence < Worksheets("infos client")(j, 2) Then
ligne = j
End If
occurence = Application.WorksheetFunction.Max(occurence, Worksheets("infos client")(j, 2))
End If
Next
If occurence = 0 Then
Set Worksheets("infos clients")(NBinfosclient + 1, 1) = Worksheets("PLDG")(i, 1)
Set Worksheets("infos clients")(NBinfosclient + 1, 2) = 0
Set Worksheets("infos clients")(NBinfosclient + 1, 3) = Worksheets("PLDG")(i, 6)
Set Worksheets("infos clients")(NBinfosclient + 1, 4) = Worksheets("date")(2, 1)
Set Worksheets("infos clients")(NBinfosclient + 1, 5) = Worksheets("PLDG")(i, 10)
Set Worksheets("CSR")(NBinfosclient + 1, 1) = Worksheets("PLDG")(i, 1)
Set Worksheets("CSR")(NBinfosclient + 1, 2) = 0
Set Worksheets("CSR")(NBinfosclient + 1, 3) = 1
Set Worksheets("CSR")(NBinfosclient + 1, 4) = Worksheets("PLDG")(i, 5)
Set Worksheets("Solde")(NBinfosclient + 1, 1) = Worksheets("PLDG")(i, 1)
Set Worksheets("Solde")(NBinfosclient + 1, 2) = 0
Set Worksheets("Solde")(NBinfosclient + 1, 3) = 1
Set Worksheets("Solde")(NBinfosclient + 1, 4) = Worksheets("PLDG")(i, 10)
Set Worksheets("provisions")(NBinfosclient + 1, 1) = Worksheets("PLDG")(i, 1)
Set Worksheets("provisions")(NBinfosclient + 1, 2) = 0
Set Worksheets("provisions")(NBinfosclient + 1, 3) = 1
Set Worksheets("provisions")(NBinfosclient + 1, 4) = Worksheets("PLDG")(i, 16)
End If
If occurence <> 0 Then
Dim a As Integer
a = Worksheets("solde")(ligne, 3)
If Worksheets("solde")(ligne, a + 3) = 0 Then
Set Worksheets("infos clients")(NBinfosclient + 1, 1) = Worksheets("PLDG")(i, 1)
Set Worksheets("infos clients")(NBinfosclient + 1, 2) = occurence + 1
Set Worksheets("infos clients")(NBinfosclient + 1, 3) = Worksheets("PLDG")(i, 6)
Set Worksheets("infos clients")(NBinfosclient + 1, 4) = Worksheets("date")(2, 1)
Set Worksheets("infos clients")(NBinfosclient + 1, 5) = Worksheets("PLDG")(i, 10)
Set Worksheets("CSR")(NBinfosclient + 1, 1) = Worksheets("PLDG")(i, 1)
Set Worksheets("CSR")(NBinfosclient + 1, 2) = occurence + 1
Set Worksheets("CSR")(NBinfosclient + 1, 3) = 1
Set Worksheets("CSR")(NBinfosclient + 1, 4) = Worksheets("PLDG")(i, 5)
Set Worksheets("Solde")(NBinfosclient + 1, 1) = Worksheets("PLDG")(i, 1)
Set Worksheets("Solde")(NBinfosclient + 1, 2) = occurence + 1
Set Worksheets("Solde")(NBinfosclient + 1, 3) = 1
Set Worksheets("Solde")(NBinfosclient + 1, 4) = Worksheets("PLDG")(i, 10)
Set Worksheets("provisions")(NBinfosclient + 1, 1) = Worksheets("PLDG")(i, 1)
Set Worksheets("provisions")(NBinfosclient + 1, 2) = occurence + 1
Set Worksheets("provisions")(NBinfosclient + 1, 3) = 1
Set Worksheets("provisions")(NBinfosclient + 1, 4) = Worksheets("PLDG")(i, 16)
End If
If Worksheets("solde")(ligne, a + 3) <> 0 Then
Dim nbCSR As Integer
Dim nbsolde As Integer
Dim nbprovision As Integer
nbCSR = Worksheets("CSR")(ligne, 3) + 1
nbsolde = Worksheets("Solde")(ligne, 3) + 1
nbprovision = Worksheets("provisions")(ligne, 3) + 1
Set Worksheets("CSR")(ligne, 3) = nbCSR
Set Worksheets("CSR")(ligne, nbCSR + 3) = Worksheets("PLDG")(i, 5)
Set Worksheets("solde")(ligne, 3) = nbsolde
Set Worksheets("Solde")(NBinfosclient + 1, nbsolde + 3) = Worksheets("PLDG")(i, 10)
Set Worksheets("provision")(ligne, 3) = nbprovision
Set Worksheets("provisions")(NBinfosclient + 1, nbprovisions + 3) = Worksheets("PLDG")(i, 16)
End If
End If
Next
End Sub
|
In case you are wondering: yes I program with my feets, yes I suck but this should be pretty obvious. I especially don't find the syntax for VBA Excel and this is even more painfull.... |
_________________ Join NL Raises from the Ashes |
|