Excel Forum

Code: Select all

Sub Macro2()
'
    
Range("C1").Select
Do Until ActiveCell.Offset(0, 19).Value = ""



If ActiveCell.Offset(0, -1).Value = "Material :" Then

    ActiveCell.FormulaR1C1 = "=RC[19]"
    ActiveCell.Offset(0, 5).Select
    
    ActiveCell.FormulaR1C1 = "=RC[15]"
    ActiveCell.Offset(0, 3).Select

   ActiveCell.FormulaR1C1 = "=RC[13]"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Offset(0, -8).Select
    
    Else
    ActiveCell.Offset(1, 0).Select
    
    End If
     ActiveCell.Offset(1, 0).Select
    
    Loop
    
    ActiveCell.Offset(1, 0).Select
End Sub

Code: Select all

Dim rall As Range
Dim r As Range, rw As Long
rw = 2
With Worksheets("Sheet2")
    Set rall = .Range("b:b").SpecialCells(xlCellTypeConstants)
    For Each r In rall
        If r.Value = "Material :" Then
            r.Offset(0, 1).Formula = "=V" & rw
            r.Offset(0, 6).Formula = "=W" & rw
            r.Offset(0, 9).Formula = "=X" & rw
            rw = rw + 1
        End If
    Next r
End With