Originally Posted by
p45cal
One, albeit not elegant, solution to the non-incremental looping could be:
[vba]Sub Pensioner_Data_Autofill(rngSrcPensioner As Range)
Dim Map, i
Map = Array("1", "4", "2", "5", "3", "6", "4", "7", "5", "8", "7", "9", "8", "10", "9", "11", "10", "12", "11", "13", "15", "14", "16", "15", "17", "16", "21", "17")
With rngSrcPensioner
For i = LBound(Map) To UBound(Map) Step 2
'Debug.Print "zz" & Map(i) & "zz,xx" & Map(i + 1) & "xx"
.Offset(CLng(Map(i)), 0).Value = Application.WorksheetFunction.VLookup(.Value, Range("SECTION7_Autofill_Data"), CLng(Map(i + 1)), False)
Next i
End With
End Sub
[/vba]I've not tested this beyond ensuring the numbers are used in the right order (commented-out debug statement); the CLng conversion may not be necessary. I leave you to put the error handling in.
p45cal
ps. to edit more easily and visualise the paired values you can do the likes of this:[vba]Map = Array("1", "4", _
"2", "5", _
"3", "6", _
"4", "7", _
"5", "8", _
"7", "9", _
"8", "10", _
"9", "11", _
"10", "12", _
"11", "13", _
"15", "14", _
"16", "15", _
"17", "16", _
"21", "17")
[/vba]