Excel Forum

Code: Select all

Sub Test0()
    Dim rAll As Range
    Dim r As Range, strSource As String
    Application.ScreenUpdating = False
    With ActiveSheet
        Set rAll = .Range("d2", .Range("d" & .Rows.Count).End(xlUp))
        For Each r In rAll
            strSource = "'" & r.Offset(0, -3) & "[" & r.Offset(0, -2) & "]" & _
                r.Offset(0, -1) & "'!"
            r.Offset(0, 9).Formula = "=" & strSource & r.Value
           r.Offset(0, 10).Formula = "=" & strSource & r.Offset(0, 1).Value
           r.Offset(0, 11).Formula = "=" & strSource & r.Offset(0, 2).Value
           r.Offset(0, 12).Formula = "=" & strSource & r.Offset(0, 3).Value
           r.Offset(0, 13).Formula = "=" & strSource & r.Offset(0, 4).Value
            r.Offset(0, 14).Formula = "=" & strSource & r.Offset(0, 5).Value
            r.Offset(0, 15).Formula = "=" & strSource & r.Offset(0, 6).Value
             r.Offset(0, 16).Formula = "=" & strSource & r.Offset(0, 7).Value

        Next r
    Application.ScreenUpdating = True
    End With
End Sub

Code: Select all

Sub Test0()
    Dim rAll As Range
    Dim r As Range, strSource As String
    Dim i As Integer
    Application.ScreenUpdating = False
    With ActiveSheet
        Set rAll = .Range("d2", .Range("d" & .Rows.Count).End(xlUp))
        For Each r In rAll
            strSource = "'" & r.Offset(0, -3) & "[" & r.Offset(0, -2) & "]" & _
                r.Offset(0, -1) & "'!"
            For i = 9 To 16
                r.Offset(0, i).Formula = "=" & strSource & r.Offset(0, i - 9).Value
            Next i
'            r.End(xlToRight).Value
        Next r
    Application.ScreenUpdating = True
    End With
End Sub