1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58
| Option Explicit
Dim cColDest As Collection
Dim shDest As Worksheet
'
' Mémorisation numéro de colonne
'
Sub InitCollection()
Dim i As Integer
Set shDest = ThisWorkbook.Worksheets("Feuil3") ' A ADAPTER
Set cColDest = New Collection
i = 1
While shDest.Cells(1, i) <> ""
On Error Resume Next
cColDest.Add i, shDest.Cells(1, i)
If Err.Number <> 0 Then 'On considére qu'il n'y as pas de doublon dans colone dest
Debug.Print "Doublon colonne " & i & " : " & shDest.Cells(1, i)
End If
i = i + 1
Wend
End Sub
'
' Dépose valeur dans feuille destination
'
Function PoseValeur(stEntete As String, MaValeur) As Boolean
Dim iC As Integer
Dim iDerLigne As Long
On Error Resume Next
iC = cColDest(stEntete)
If iC = 0 Or Err.Number <> 0 Then
Debug.Print "Erreur Entête " & stEntete & " Introuvable"
PoseValeur = False
Exit Function
End If
On Error GoTo 0
iDerLigne = shDest.Cells(shDest.Rows.Count, iC).End(xlUp).Row + 1
shDest.Cells(iDerLigne, iC).Value = MaValeur
PoseValeur = True
End Function
'
' FONCTION DE TEST ....
'
Sub MaMAcro()
InitCollection
Debug.Print PoseValeur("Nom", "AjoutDansColNOM")
Debug.Print PoseValeur("Adresse", "AjoutDansColonne Adresse")
End Sub |
Partager