An Excel is created and sent for each teacher. The number of columns in the Excel is variable. This works for the first record, but for the second record I get the error message 1004: Method Range of object _Global failed. The last thing I have done now is the Range "rng" set to .clear because I thought the address of the Range was retained. The "Lbound" and "UBound" are always correct. The array "arr (i)" is also retrieved correctly. Don't see the problem. See marked as red.
Code:
With XL
.Visible = False
.DisplayAlerts = False
With OpenWerkboek
For i = 1 To iAantalDomeinen
ReDim Preserve arr(i)
arr(i) = Mid(txtNaamKolom, i, 1)
Next i
For i = LBound(arr) To UBound(arr)
If i = LBound(arr) Then
[U][B][COLOR=Red] Set rng = Range("" & arr(i) & "2:" & arr(i) & iAantalLeerlingen & "") [/COLOR][/B][/U]
Else
Set rng = Union(rng, Range("" & arr(i) & "2:" & arr(i) & iAantalLeerlingen & ""))
End If
Next i
With rng.Validation
.Delete
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=iMinPunten, Formula2:=iMaxPunten
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Punten ingeven"
.ErrorTitle = "Foute ingave"
.InputMessage = "Geef hier je punten in."
.ErrorMessage = "Geef juiste waarde in!"
.ShowInput = True
.ShowError = True
End With
End With
OpenWerkboek.Save
End With