error 1004 (1 Viewer)

so10070

Registered User.
Local time
Today, 21:08
Joined
Aug 18, 2016
Messages
51
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
 

missinglinq

AWF VIP
Local time
Today, 16:08
Joined
Jun 20, 2003
Messages
6,423
I suspect that the reason over 50 members have read your post without answering is that it appears to be a straight EXCEL problem...and this is an ACCESS forum!

If it is strictly Excel you need to post it in an Excel forum.

If it is an Access problem, you need to post all of your code for this event, showing how you're invoking Excel from Access.

Linq ;0)>
 

so10070

Registered User.
Local time
Today, 21:08
Joined
Aug 18, 2016
Messages
51
Have found the sollution! I had declared only one time a numeric parameter and used it in two different ways in the procedure. I have declared a second parameter and the procedure is working well.
It was definitely an Access problem!
Ok, in the future I will update an example program.
 

Gasman

Enthusiastic Amateur
Local time
Today, 20:08
Joined
Sep 21, 2011
Messages
14,047
Also what is the value of iAantalLeerlingen ?
 

Gasman

Enthusiastic Amateur
Local time
Today, 20:08
Joined
Sep 21, 2011
Messages
14,047
Have found the sollution! I had declared only one time a numeric parameter and used it in two different ways in the procedure. I have declared a second parameter and the procedure is working well.
It was definitely an Access problem!
Ok, in the future I will update an example program.

How is that an Access problem?
 

so10070

Registered User.
Local time
Today, 21:08
Joined
Aug 18, 2016
Messages
51
The numeric value was used in two places (two loops) in the procedure: once to cycle trough a table and once to set dynamically (based on user input) the size of the range in the workbook.
 

Gasman

Enthusiastic Amateur
Local time
Today, 20:08
Joined
Sep 21, 2011
Messages
14,047
My guess is that, despite working with Excel, this code was part of an Access application.

No, I meant, the O/P used the same variable for different purposes and that was the root of the problem, not the fault of Access. :D

The same problem could have happened in Excel, and it would not be an Excel problem.?

When silly errors like that happen to me, I always step through the code and inspect each variable. That method has always shown me my mistake. :)
 

Users who are viewing this thread

Top Bottom