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 59 60 61 62 63 64 65 66
|
Option Explicit
Public DetectionColonnes As String
Function ColonneFeuille(ByVal FeuilleTitre As Worksheet, ByVal LigneTitre As Long, ByVal TitreRecherche As String) As Long
Dim NbColonnes As Long
Dim Cellule As Range
Dim Aire As Range
With FeuilleTitre
ColonneFeuille = 0
NbColonnes = .Cells(LigneTitre, .Columns.Count).End(xlToLeft).Column
Set Aire = .Range(.Cells(LigneTitre, 1), .Cells(LigneTitre, NbColonnes))
For Each Cellule In Aire
Select Case Mid(Cellule.Value, 1, Len(TitreRecherche))
Case TitreRecherche
ColonneFeuille = Cellule.Column
Exit For
End Select
Next
If ColonneFeuille = 0 Then DetectionColonnes = DetectionColonnes & Chr(10) & TitreRecherche
Set Aire = Nothing
End With
End Function
Sub Test()
Dim ShVentes As Worksheet
Dim LigneDeTitre As Long
Dim ColInfo1 As Long, ColInfo2 As Long
On Error GoTo FinTest
DetectionColonnes = "Absence colonnes : " & Chr(10)
Set ShVentes = Sheets("Base ventes")
With ShVentes
LigneDeTitre = .ListObjects("Export_ventes").HeaderRowRange.Row
ColInfo1 = ColonneFeuille(ShVentes, LigneDeTitre, "Info1")
ColInfo2 = ColonneFeuille(ShVentes, LigneDeTitre, "Info2")
End With
If DetectionColonnes <> "Absence colonnes : " & Chr(10) Then
MsgBox DetectionColonnes
GoTo FinTest
End If
MsgBox ColInfo1 & Chr(10) & ColInfo2
GoTo FinTest
FinTest:
Set ShVentes = Nothing
End Sub |
Partager