De juiste syntax voor de formule lukt wel. In feite heb ik de makro wel werkend gekregen zonder geneste lus, maar dan krijg ik wel veel herhalende tekst in de makro. Hij moet nog veel langer worden als hij nu is, maar dit is in ieder geval een goed functionerend begin:
Sub koppeling()
Sheets("persoonlijke gegevens").Select
Dim r As Range
For Each r In Range("B2:B8")
Range("B" & r.Row).Select
ActiveCell.Formula = "='D:\clienten\client " & Format(r.Row - 1, "000") & "\[client " & Format(r.Row - 1, "000") & ".xls]client'!B1"
Range("C" & r.Row).Select
ActiveCell.Formula = "='D:\clienten\client " & Format(r.Row - 1, "000") & "\[client " & Format(r.Row - 1, "000") & ".xls]client'!B2"
Range("D" & r.Row).Select
ActiveCell.Formula = "='D:\clienten\client " & Format(r.Row - 1, "000") & "\[client " & Format(r.Row - 1, "000") & ".xls]client'!B3"
Range("E" & r.Row).Select
ActiveCell.Formula = "='D:\clienten\client " & Format(r.Row - 1, "000") & "\[client " & Format(r.Row - 1, "000") & ".xls]client'!B4"
Range("F" & r.Row).Select
ActiveCell.Formula = "='D:\clienten\client " & Format(r.Row - 1, "000") & "\[client " & Format(r.Row - 1, "000") & ".xls]client'!B5"
Range("G" & r.Row).Select
ActiveCell.Formula = "='D:\clienten\client " & Format(r.Row - 1, "000") & "\[client " & Format(r.Row - 1, "000") & ".xls]client'!B6"
Range("H" & r.Row).Select
ActiveCell.Formula = "='D:\clienten\client " & Format(r.Row - 1, "000") & "\[client " & Format(r.Row - 1, "000") & ".xls]client'!B7"
Range("I" & r.Row).Select
ActiveCell.Formula = "='D:\clienten\client " & Format(r.Row - 1, "000") & "\[client " & Format(r.Row - 1, "000") & ".xls]client'!B8"
Range("J" & r.Row).Select
ActiveCell.Formula = "='D:\clienten\client " & Format(r.Row - 1, "000") & "\[client " & Format(r.Row - 1, "000") & ".xls]client'!B9"
Range("K" & r.Row).Select
ActiveCell.Formula = "='D:\clienten\client " & Format(r.Row - 1, "000") & "\[client " & Format(r.Row - 1, "000") & ".xls]client'!B10"
Range("L" & r.Row).Select
ActiveCell.Formula = "='D:\clienten\client " & Format(r.Row - 1, "000") & "\[client " & Format(r.Row - 1, "000") & ".xls]client'!B11"
Range("M" & r.Row).Select
ActiveCell.Formula = "='D:\clienten\client " & Format(r.Row - 1, "000") & "\[client " & Format(r.Row - 1, "000") & ".xls]client'!B12"
Range("N" & r.Row).Select
ActiveCell.Formula = "='D:\clienten\client " & Format(r.Row - 1, "000") & "\[client " & Format(r.Row - 1, "000") & ".xls]client'!B13"
Range("O" & r.Row).Select
ActiveCell.Formula = "='D:\clienten\client " & Format(r.Row - 1, "000") & "\[client " & Format(r.Row - 1, "000") & ".xls]client'!B14"
Range("P" & r.Row).Select
ActiveCell.Formula = "='D:\clienten\client " & Format(r.Row - 1, "000") & "\[client " & Format(r.Row - 1, "000") & ".xls]client'!B15"
Range("Q" & r.Row).Select
ActiveCell.Formula = "='D:\clienten\client " & Format(r.Row - 1, "000") & "\[client " & Format(r.Row - 1, "000") & ".xls]client'!B16"
Range("R" & r.Row).Select
ActiveCell.Formula = "='D:\clienten\client " & Format(r.Row - 1, "000") & "\[client " & Format(r.Row - 1, "000") & ".xls]client'!B17"
Range("S" & r.Row).Select
ActiveCell.Formula = "='D:\clienten\client " & Format(r.Row - 1, "000") & "\[client " & Format(r.Row - 1, "000") & ".xls]client'!B18"
Range("T" & r.Row).Select
ActiveCell.Formula = "='D:\clienten\client " & Format(r.Row - 1, "000") & "\[client " & Format(r.Row - 1, "000") & ".xls]client'!B19"
Range("U" & r.Row).Select
ActiveCell.Formula = "='D:\clienten\client " & Format(r.Row - 1, "000") & "\[client " & Format(r.Row - 1, "000") & ".xls]client'!B20"
Next
End Sub
Ik had alleen het idee dat het in een geneste lus veel efficienter zou moeten kunnen.