In een loop een formule wegschrijven naar cellen en celnummers door laten nummeren

[RikBakhuizen]

Beste,

Ik heb het volgende probleem waar ik niet uit kom:
In het onderstaande stukje schrijf ik in een loop deze formules naar een bepaalde cel.
Nu moet als voorbeeld elke rij na de nieuwe loop in te gaan ook met 1 verhoogd worden.

Cells(i, 2).Formula = "='TS First'!K2"

K2 moet na de eerste loop dus verhoofd worden en in de volgende loop K3 worden en zo voor allemaal!
Wie weet dit hoe het best op te lossen?

Sub CalculateValues()
        Dim ws As Worksheet
        Dim i As Integer
        Dim y As Integer
        Set ws = ActiveSheet
        Worksheets("RPH CALC").Activate
        'Loop to put all TS First data into overview calculation sheet
         For i = 10 To 3650
            Cells(i, 2).Formula = "='TS First'!K2"
            Cells(i, 3).Formula = "='TS First'!B2"
            Cells(i, 4).Formula = "='TS First'!C2"
            Cells(i, 5).Formula = "='TS First'!D2"
            Cells(i, 6).Formula = "='TS First'!A2"
            'Loop to search and match by Vlookup all TS second data into overview calculation sheet
            Cells(i, 8).Formula = "='TS Second'!K2"
            Cells(i, 9).Formula = "='TS Second'!B2"
            Cells(i, 10).Formula = "='TS Second'!C2"
            Cells(i, 11).Formula = "='TS Second'!D2"
            Cells(i, 12).Formula = "='TS Second'!D2 - heightdiffPitch"  'Local Pitch height difference to be taken into acount
            Cells(i, 13).Formula = "='TS Second'!A2"
            'Loop to put all TS Third data into overview calculation sheet
            Cells(i, 15).Formula = "='TS Third'!K2"
            Cells(i, 16).Formula = "='TS Third'!B2"
            Cells(i, 17).Formula = "='TS Third'!C2"
            Cells(i, 18).Formula = "='TS Third'!D2"
            Cells(i, 19).Formula = "='TS Third'!A2"
            'Loop to search and match by Vlookup all TS Fourth data into overview calculation sheet
            Cells(i, 21).Formula = "='TS Fourth'!K2"
            Cells(i, 22).Formula = "='TS Fourth'!B2"
            Cells(i, 23).Formula = "='TS Fourth'!C2"
            Cells(i, 24).Formula = "='TS Fourth'!D2"
            Cells(i, 25).Formula = "='TS Fourth'!D2 - heightdiffRoll"  'Local Roll height difference to be taken into acount
            Cells(i, 26).Formula = "='TS Fourth'!A2"
            'Loop to calculate the surveyed values and put data into overview calculation sheet
            Cells(i, 28).Formula = "=BEARING(C9:D9,I9:J9)+MissAllignment" 'GN Bearing calculation
            Cells(i, 29).Formula = "=AB9+MeridianConvergence" 'TN Bearing calculation
            Cells(i, 30).Formula = "=DEGREES(ATAN(E9-L9)/pitchDistance)" 'Pitch calculation
            Cells(i, 31).Formula = "=DEGREES(ATAN(R9-Y9)/rollDistance)" 'Roll calculation
        Next i
End Sub

 

[VenA]

Gebruik geen loop

Sub VenA()
  Range("D1").Formula = "=Sheet1!A1"
  Range("E1").Formula = "=Sheet1!C3"
  Range("D1:E1").AutoFill Range("D1:E10")
End Sub

of

Sub VenA()
  Range("A1:B10").Formula = Array("=Sheet1!A1", "=Sheet1!C3")
End Sub