35 coaches online • Server time: 01:58
Forum Chat
Log in
Recent Forum Topics goto Post Gnome Roster - how a...goto Post Skittles' Centu...goto Post Gnomes are trash
SearchSearch 
Post new topic   Reply to topic
View previous topic Log in to check your private messages View next topic
sk8bcn



Joined: Apr 13, 2004

Post   Posted: Apr 04, 2006 - 10:13 Reply with quote Back to top

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
Display posts from previous:     
 Jump to:   
All times are GMT + 1 Hour
Post new topic   Reply to topic
View previous topic Log in to check your private messages View next topic