VBA EXCEL valeur cellule d'un tableau

Function RechercheDureeVie(lampe As String, ballast As String, cycle As String) As String

    Dim numColonne As Integer
    Dim numLigne As Integer
    Dim ballastCycle As String
    
    Application.Volatile           ' Permet l'exécution à chaque intervention sur la feuille
    
    'concatener ballast et cycle
    ballastCycle = ballast & "_" & cycle
    
    'Worksheets("Durée_vie_lampes.xls").UsedRange.Select
    Workbooks("Durée_vie_lampes.xls").Activate
    Sheets("Feuil1").Select
    
    numColonne = Selection.Find(lampe, ActiveCell, xlValues, xlPart, xlByRows, xlNext, False).Column
    numLigne = Selection.Find(ballastCycle, ActiveCell, xlValues, xlPart, xlByRows, xlNext, False).Row
    
    If (ActiveSheet.Range(numLigne, numColonne).Value <> "") Then
        RechercheDureeVie = ActiveSheet.Range(numLigne, numColonne).Value
    Else
        RechercheDureeVie = "Nil"
    End If

End Function
'

Sub TranscritFormule()

    Dim Plage As Range, Cible As Range
    Dim Formule As String
    
    ' Spécifié ici la plage à couvrir
    Set Plage = Range("A1:AT146")
    
    ' Renseigner ici la variable par la formule
    Formule = "=SUM(A1:A2)"
    
    For Each Cible In Plage
        Cible.FormulaR1C1 = Formule
    Next Cible
    
End Sub

Sub TranscritFormule()

    Dim Plage As Range, Cible As Range
    Dim Formule As String
    Dim Col1 As String, Col2 As String
    Dim Lig1 As Long, Lig2 As Long
    
    Set Plage = Range("AC11:BV146")
  
    For Each Cible In Plage
        Col1 = 1
        Col2 = 2
        Lig1 = Cible.Row
        Lig2 = 1
        
        Formule = "=NB.SI(R11:R45475;CONCATENER(" & Col1 & Lig1 & ";" & Col2 & Lig2 & "))"
        Cible.FormulaR1C1 = Formule
    Next Cible
    
End Sub
'

Option Explicit

Sub TranscritFormule()

    Dim Plage As Range, Cible As Range
    Dim Formule As String
    Dim Col1 As String, Col2 As String
    Dim Lig1 As Long, Lig2 As Long
    Dim Compteur As Double
    
    Compteur = 0
    Application.ScreenUpdating = False
    Set Plage = Range("AC11:BV146")
    For Each Cible In Plage
        Col1 = ChercheColonne(Cible):   Lig1 = Cible.Row
        Col2 = "W":     Lig2 = Cible.Column
        Formule = "=COUNTIF(R11:R45475;CONCATENER(" & Col1 & Lig1 & ";" & Col2 & Lig2 & "))"
        Cible.Clear
        Cible.FormulaR1C1Local = Formule
        Compteur = (Compteur + 1)
    Next Cible
    Application.ScreenUpdating = False
    
    MsgBox "Fin" & vbLf & Compteur
    
End Sub
'

Private Function ChercheColonne(ByVal Cellule As Range) As String

    Dim strAdresse As String, Colonne As String
    Dim Position As Long, ascCode As Integer
    
    ChercheColonne = ""
    strAdresse = Cellule.Address
    Position = InStr(1, strAdresse, "$", vbTextCompare)
    strAdresse = Mid(strAdresse, (Position + 1))
    Position = InStr(1, strAdresse, "$", vbTextCompare)
    strAdresse = Mid(strAdresse, 1, (Position - 1))
    If (Len(strAdresse) > 1) Then
        ascCode = (Asc(Mid(strAdresse, 2, 1)) - 1)
        If (Mid(LCase(strAdresse), 2, 1) = "a") Then
            ascCode = (ascCode + 1)
        End If
        strAdresse = Mid(strAdresse, 1, 1) & Chr(ascCode)
    Else
        ascCode = (Asc(strAdresse) - 1)
        If (LCase(strAdresse) = "a") Then
            ascCode = (ascCode + 1)
        End If
        strAdresse = Chr(ascCode)
    End If
    ChercheColonne = strAdresse

End Function
'