XL 2016 Insérer une formule en VBA

pierrejean à dit:

Re

Macro a tester

Code:

Sub recopie()
Application.ScreenUpdating = False
derlin = Range("B" & Rows.Count).End(xlUp).Row
ReDim tablo(derlin - 9, 1)
debform = "=SI($E"
finform = "=""X"";"""";""1"")"
a = 9
For n = LBound(tablo, 1) To UBound(tablo, 1)
   tablo(n, 1) = debform & a & finform
   a = a + 1
Next
For n = LBound(tablo, 1) To UBound(tablo, 1)
   Range("I" & n + 9).FormulaLocal = tablo(n, 1)
Next
Application.ScreenUpdating = True
End Sub

Cliquez pour agrandir...

sousou à dit:

J'avais recopier la formule essaie comme ceci
zone.FormulaR1C1 = "=IF(RC[-4]=""X"","""",""1"")"

Cliquez pour agrandir...

cathodique à dit:

Re,

un essai

VB:

Sub ActifCalc()
  Dim drl As Long
  With Sheets("all")
  drl = .Range("B" & Rows.Count).End(xlUp).Row
  Set zone = .Range(.Cells(8, 9), .Cells(drl, 9))
  zone.FormulaR1C1 = "=if(rc[-4]=""X"","""",""1"")"
  End With
End Sub

Cliquez pour agrandir...

job75 à dit:

Bonjour à tous,

Laborieux ce fil, pourtant avec les Application.Match (EQUIV) c'est assez simple :

Code:

Sub RemplirColonne()
Dim deb As Range, derlig1 As Long, derlig2 As Long, derlig As Long
With Sheets("All") 'à adapter
    Set deb = .[I8] 'à adapter
    On Error Resume Next
    derlig1 = Application.Match("zzz", .Columns("B"))
    derlig2 = Application.Match(9 ^ 9, .Columns("B"))
    On Error GoTo 0
    derlig = IIf(derlig1 > derlig2, derlig1, derlig2)
    Application.ScreenUpdating = False
    deb.Resize(.Rows.Count - deb.Row + 1).ClearContents 'RAZ
    If derlig >= deb.Row Then
        deb = "=IF(E" & deb.Row & "=""X"","""",1)"
        deb.AutoFill deb.Resize(derlig - deb.Row + 1)
    End If
End With
End Sub

A+

Cliquez pour agrandir...