View Full Version : [SOLVED:] What is the maximum length for a procedure
Regouin
03-14-2005, 04:23 AM
I have written a private sub, but when I want to compile it VBA tells me that the procedure is too long, does anyone know the maximum length and/or a way to work around this. The sub is about 2800 lines :yes But it only uses a portion of the sub since it jumps around in the sub. Any help would be appreciated.
TIA
Frank
Ok, the maximum procedure length (or size) is 64kb but how do i translate this into number of characters?
Regouin: the proc length error may be a blessing in disguise. 2800 lines is a VERY LARGE procedure and, I suspect, a nightmare to maintain. It would be much easier to develop, test, manage and maintain if it were broken down into subroutines. Modularizing your code also encourages modular/reusable thinking.
Everyone has their own rule of thumb regarding procedure length. Mine is somewhere around 100 lines. It used to be 60 when 60 lines of Courier/10 fit nicely on an 8.5x11 page.
Regouin
03-14-2005, 06:31 AM
I know it is large, but it is sort of repetitive but yet not quite, I have been trying to put the code on a diet, but best i have achieved is to bring it down to 2400, at the moment I am trying to break it down into subroutines, but now I have redirections in the procedure, so if it would run it It only uses about 2% of the lines at the most, only problem is that there are about 60 different routes it can take.
Well, at least you are trying to modularize it. If the code is not very sensitive, you might post it to the forum asking for modularization suggestions.
FYI: in the very long time I have been writing code (I started with FORTRAN IV when 16k was a lot of memory), I have learned a few lessons (some more than once). One of the major reasons applications die is because the original developer has left, something has gone wrong (or the environment has changed or a new feature is required or ... ) and the code is too hard to figure out.
Regouin
03-14-2005, 08:15 AM
do you really want me to post 2800 lines of code, I think I figured it out, instead of using
if ...=... then goto blabla
I used
if ...=... then call blabla
now this seems to work perfectly
I wouldnt mind if you would like to take a look at the code.
Private Sub cboxonderdeel_Change()
cboxonderhoud.Clear
With cboxonderhoud
.ColumnCount = 1
.ColumnWidths = 280
.Width = 300
.Height = 15
.ListRows = 17
End With
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
If cboxonderdeel.Text = .Range("a2") Then Call lvwas1
If cboxonderdeel.Text = .Range("a45") Then Call lvwas2
If cboxonderdeel.Text = .Range("a90") Then Call lvwas3
If cboxonderdeel.Text = .Range("a135") Then Call lvwas4
If cboxonderdeel.Text = .Range("a180") Then Call lvwas5
If cboxonderdeel.Text = .Range("a225") Then Call lvwas6
If cboxonderdeel.Text = .Range("a270") Then Call lvwas7
If cboxonderdeel.Text = .Range("a315") Then Call lvwas8
If cboxonderdeel.Text = .Range("a360") Then Call lvwas9
If cboxonderdeel.Text = .Range("a405") Then Call lvwas10
If cboxonderdeel.Text = .Range("a450") Then Call lvwas11
If cboxonderdeel.Text = .Range("a495") Then Call lvwas12
If cboxonderdeel.Text = .Range("a540") Then Call lvwas13
If cboxonderdeel.Text = .Range("a585") Then Call lvwas14
If cboxonderdeel.Text = .Range("a630") Then Call lvwas15
If cboxonderdeel.Text = .Range("a675") Then Call lvwas16
If cboxonderdeel.Text = .Range("a720") Then Call lvwas17
If cboxonderdeel.Text = .Range("a765") Then Call lvwas18
If cboxonderdeel.Text = .Range("a811") Then Call rvwas1
If cboxonderdeel.Text = .Range("a854") Then Call rvwas2
If cboxonderdeel.Text = .Range("a899") Then Call rvwas3
If cboxonderdeel.Text = .Range("a944") Then Call rvwas4
If cboxonderdeel.Text = .Range("a989") Then Call rvwas5
If cboxonderdeel.Text = .Range("a1034") Then Call rvwas6
If cboxonderdeel.Text = .Range("a1079") Then Call rvwas7
If cboxonderdeel.Text = .Range("a1124") Then Call rvwas8
If cboxonderdeel.Text = .Range("a1169") Then Call rvwas9
If cboxonderdeel.Text = .Range("a1214") Then Call rvwas10
If cboxonderdeel.Text = .Range("a1259") Then Call rvwas11
If cboxonderdeel.Text = .Range("a1304") Then Call rvwas12
If cboxonderdeel.Text = .Range("a1349") Then Call rvwas13
If cboxonderdeel.Text = .Range("a1394") Then Call rvwas14
If cboxonderdeel.Text = .Range("a1439") Then Call rvwas15
If cboxonderdeel.Text = .Range("a1484") Then Call rvwas16
If cboxonderdeel.Text = .Range("a1529") Then Call rvwas17
If cboxonderdeel.Text = .Range("a1575") Then Call hwas11
If cboxonderdeel.Text = .Range("a1620") Then Call hwas12
If cboxonderdeel.Text = .Range("a1665") Then Call hwas13
If cboxonderdeel.Text = .Range("a1710") Then Call hwas14
If cboxonderdeel.Text = .Range("a1755") Then Call hwas15
If cboxonderdeel.Text = .Range("a1800") Then Call hwas16
If cboxonderdeel.Text = .Range("a1845") Then Call hwas17
If cboxonderdeel.Text = .Range("a1890") Then Call hwas18
If cboxonderdeel.Text = .Range("a1935") Then Call hwas19
If cboxonderdeel.Text = .Range("a1980") Then Call hwas110
If cboxonderdeel.Text = .Range("a2025") Then Call hwas111
If cboxonderdeel.Text = .Range("a2070") Then Call hwas112
If cboxonderdeel.Text = .Range("a2115") Then Call hwas113
If cboxonderdeel.Text = .Range("a2160") Then Call hwas114
If cboxonderdeel.Text = .Range("a2206") Then Call hwas21
If cboxonderdeel.Text = .Range("a2251") Then Call hwas22
If cboxonderdeel.Text = .Range("a2296") Then Call hwas23
If cboxonderdeel.Text = .Range("a2341") Then Call hwas24
If cboxonderdeel.Text = .Range("a2386") Then Call hwas25
If cboxonderdeel.Text = .Range("a2431") Then Call hwas26
If cboxonderdeel.Text = .Range("a2476") Then Call hwas27
If cboxonderdeel.Text = .Range("a2521") Then Call hwas28
If cboxonderdeel.Text = .Range("a2566") Then Call hwas29
If cboxonderdeel.Text = .Range("a2611") Then Call hwas210
If cboxonderdeel.Text = .Range("a2656") Then Call hwas211
If cboxonderdeel.Text = .Range("a2701") Then Call hwas212
If cboxonderdeel.Text = .Range("a2746") Then Call hwas213
If cboxonderdeel.Text = .Range("a2791") Then Call hwas214
End With
End Sub
Private Sub lvwas1()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A44").End(xlUp).Row
For i = 13 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub lvwas2()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A89").End(xlUp).Row
For i = 56 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub lvwas3()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A134").End(xlUp).Row
For i = 101 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub lvwas4()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A179").End(xlUp).Row
For i = 146 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub lvwas5()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A224").End(xlUp).Row
For i = 191 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub lvwas6()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A269").End(xlUp).Row
For i = 236 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub lvwas7()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A214").End(xlUp).Row
For i = 281 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub lvwas8()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A359").End(xlUp).Row
For i = 326 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub lvwas9()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A404").End(xlUp).Row
For i = 371 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub lvwas10()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A449").End(xlUp).Row
For i = 416 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub lvwas11()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A494").End(xlUp).Row
For i = 461 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub lvwas12()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A539").End(xlUp).Row
For i = 506 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub lvwas13()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A584").End(xlUp).Row
For i = 551 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub lvwas14()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A629").End(xlUp).Row
For i = 596 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub lvwas15()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A674").End(xlUp).Row
For i = 641 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub lvwas16()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A719").End(xlUp).Row
For i = 686 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub lvwas17()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A764").End(xlUp).Row
For i = 731 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub lvwas18()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A809").End(xlUp).Row
For i = 776 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub rvwas1()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A853").End(xlUp).Row
For i = 822 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub rvwas2()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A898").End(xlUp).Row
For i = 865 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub rvwas3()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A943").End(xlUp).Row
For i = 910 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub rvwas4()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A988").End(xlUp).Row
For i = 955 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub rvwas5()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A1033").End(xlUp).Row
For i = 1000 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub rvwas6()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A1078").End(xlUp).Row
For i = 1045 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub rvwas7()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A1123").End(xlUp).Row
For i = 1090 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub rvwas8()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A1168").End(xlUp).Row
For i = 1135 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub rvwas9()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A1213").End(xlUp).Row
For i = 1180 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub rvwas10()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A1258").End(xlUp).Row
For i = 1225 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub rvwas11()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A1303").End(xlUp).Row
For i = 1270 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub rvwas12()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A1348").End(xlUp).Row
For i = 1315 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub rvwas13()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A1393").End(xlUp).Row
For i = 1360 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub rvwas14()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A1438").End(xlUp).Row
For i = 1405 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub rvwas15()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A1483").End(xlUp).Row
For i = 1450 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub rvwas16()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A1528").End(xlUp).Row
For i = 1495 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub rvwas17()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A1573").End(xlUp).Row
For i = 1540 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub hwas11()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A1619").End(xlUp).Row
For i = 1586 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub hwas12()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A1664").End(xlUp).Row
For i = 1631 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub hwas13()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A1709").End(xlUp).Row
For i = 1676 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub hwas14()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A1754").End(xlUp).Row
For i = 1721 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub hwas15()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A1799").End(xlUp).Row
For i = 1766 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub hwas16()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A1844").End(xlUp).Row
For i = 1811 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub hwas17()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A1889").End(xlUp).Row
For i = 1856 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub hwas18()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A1934").End(xlUp).Row
For i = 1901 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub hwas19()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A1979").End(xlUp).Row
For i = 1946 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub hwas110()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A2024").End(xlUp).Row
For i = 1991 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub hwas111()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A2069").End(xlUp).Row
For i = 2036 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub hwas112()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A2114").End(xlUp).Row
For i = 2081 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub hwas113()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A2159").End(xlUp).Row
For i = 2126 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub hwas114()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A2204").End(xlUp).Row
For i = 2171 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub hwas21()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A2250").End(xlUp).Row
For i = 2217 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub hwas22()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A2295").End(xlUp).Row
For i = 2262 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub hwas23()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A2340").End(xlUp).Row
For i = 2307 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub hwas24()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A2385").End(xlUp).Row
For i = 2352 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub hwas25()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A2430").End(xlUp).Row
For i = 2397 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub hwas26()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A2475").End(xlUp).Row
For i = 2442 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub hwas27()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A2520").End(xlUp).Row
For i = 2487 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub hwas28()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A2565").End(xlUp).Row
For i = 2532 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub hwas29()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A2610").End(xlUp).Row
For i = 2577 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub hwas210()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A2655").End(xlUp).Row
For i = 2622 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub hwas211()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A2700").End(xlUp).Row
For i = 2667 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub hwas212()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A2745").End(xlUp).Row
For i = 2712 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub hwas213()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A2790").End(xlUp).Row
For i = 2757 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Private Sub hwas214()
With Worksheets("onderhoud")
Dim i As Long
Dim LastRow As Long
LastRow = Worksheets("onderhoud").Range("A2835").End(xlUp).Row
For i = 2802 To LastRow
If .Range("A" & i).Value <> "" And _
.Range("A" & i).Value <> .Range("D2") And _
.Range("A" & i).Value <> .Range("D3") And _
.Range("A" & i).Value <> .Range("D4") And _
.Range("A" & i).Value <> .Range("D5") And _
.Range("A" & i).Value <> .Range("D6") And _
.Range("A" & i).Value <> .Range("D7") And _
.Range("A" & i).Value <> .Range("E2") And _
.Range("A" & i).Value <> .Range("E3") And _
.Range("A" & i).Value <> .Range("E4") And _
.Range("A" & i).Value <> .Range("E5") And _
.Range("A" & i).Value <> .Range("E6") And _
.Range("A" & i).Value <> .Range("E7") And _
.Range("A" & i).Value <> .Range("E8") And _
.Range("A" & i).Value <> .Range("E9") And _
.Range("A" & i).Value <> .Range("E10") And _
.Range("A" & i).Value <> .Range("F2") And _
.Range("A" & i).Value <> .Range("F3") And _
.Range("A" & i).Value <> .Range("F4") And _
.Range("A" & i).Value <> .Range("F5") And _
.Range("A" & i).Value <> .Range("F6") And _
.Range("A" & i).Value <> .Range("F7") And _
.Range("A" & i).Value <> .Range("F8") And _
.Range("A" & i).Value <> .Range("F9") And _
.Range("A" & i).Value <> .Range("F10") Then
Me.cboxonderhoud.AddItem Worksheets("onderhoud").Range("A" & i).Text
End If
Next i
End With
End Sub
Now there is a lot of repetitive stuff in there
Paleo
03-14-2005, 08:20 AM
Ok, the maximum procedure length (or size) is 64kb but how do i translate this into number of characters?
64 Kb = 1,024 bytes x 64 = 65,536 bytes
As a byte equals a character this means your sub may have 65,536 characters. By coincidence or not the same number of rows your worksheet has.
Regouin
03-14-2005, 08:29 AM
Thats not a coincidence, that has to do with the amount of binary memory positions available. Both rows and columns (256 (number of columns) and 65536 (number of rows)) are maximum binary numbers (i.e. 1111111111) + 1 which in binary is represented by 0.
for 256 it means 2^8 and for 65536 means 2^16. Both also happen to be hexadecimal numbers, this is because a computer uses slots of 8 binary numbers for memory and 8 binary numbers represent 1 hexadecimal number.
I hope I am correct with all this jibberish, but I thought it was something like that.
Paleo
03-14-2005, 08:34 AM
By coincidence or not the same number of rows your worksheet has.
I know thats not a coincidence Regouin, after all whats a coincidence in informatics? As long as I know, nothing. But thats okay, I must have made you laugh with that phrase, so its okay :friends:
Norie
03-14-2005, 08:56 AM
Why not create a sub routine that takes the start value for I and the row in A as parameters?
That seems to be the only difference between the hwasN procedures.
By the way the code compiles when I try it in Excel 2000.
mvidas
03-14-2005, 09:48 AM
Regouin,
Can your cboxonderdeel.Text ever equal more than one range in onderhoud? Or will it only equal one of the range values?
Matt
Instead of waiting for your reply, I'm just going to post some updated code, and then what you'd change if it would only be one.
As Norie said, you can condense your code to:
Private Sub cboxonderdeel_Change()
cboxonderhoud.Clear
With cboxonderhoud
.ColumnCount = 1
.ColumnWidths = 280
.Width = 300
.Height = 15
.ListRows = 17
End With
With Worksheets("onderhoud")
If cboxonderdeel.Text = .Range("a2") Then Call lvwasx(44, 13)
If cboxonderdeel.Text = .Range("a45") Then Call lvwasx(89, 56)
If cboxonderdeel.Text = .Range("a90") Then Call lvwasx(134, 101)
If cboxonderdeel.Text = .Range("a135") Then Call lvwasx(179, 146)
If cboxonderdeel.Text = .Range("a180") Then Call lvwasx(224, 191)
If cboxonderdeel.Text = .Range("a225") Then Call lvwasx(269, 236)
If cboxonderdeel.Text = .Range("a270") Then Call lvwasx(314, 281)
If cboxonderdeel.Text = .Range("a315") Then Call lvwasx(359, 326)
If cboxonderdeel.Text = .Range("a360") Then Call lvwasx(404, 371)
If cboxonderdeel.Text = .Range("a405") Then Call lvwasx(449, 416)
If cboxonderdeel.Text = .Range("a450") Then Call lvwasx(494, 461)
If cboxonderdeel.Text = .Range("a495") Then Call lvwasx(539, 506)
If cboxonderdeel.Text = .Range("a540") Then Call lvwasx(584, 551)
If cboxonderdeel.Text = .Range("a585") Then Call lvwasx(629, 596)
If cboxonderdeel.Text = .Range("a630") Then Call lvwasx(674, 641)
If cboxonderdeel.Text = .Range("a675") Then Call lvwasx(719, 686)
If cboxonderdeel.Text = .Range("a720") Then Call lvwasx(764, 731)
If cboxonderdeel.Text = .Range("a765") Then Call lvwasx(809, 776)
If cboxonderdeel.Text = .Range("a811") Then Call lvwasx(853, 822)
If cboxonderdeel.Text = .Range("a854") Then Call lvwasx(898, 867)
If cboxonderdeel.Text = .Range("a899") Then Call lvwasx(943, 912)
If cboxonderdeel.Text = .Range("a944") Then Call lvwasx(988, 957)
If cboxonderdeel.Text = .Range("a989") Then Call lvwasx(1033, 1002)
If cboxonderdeel.Text = .Range("a1034") Then Call lvwasx(1078, 1047)
If cboxonderdeel.Text = .Range("a1079") Then Call lvwasx(1123, 1092)
If cboxonderdeel.Text = .Range("a1124") Then Call lvwasx(1168, 1137)
If cboxonderdeel.Text = .Range("a1169") Then Call lvwasx(1213, 1182)
If cboxonderdeel.Text = .Range("a1214") Then Call lvwasx(1258, 1227)
If cboxonderdeel.Text = .Range("a1259") Then Call lvwasx(1303, 1272)
If cboxonderdeel.Text = .Range("a1304") Then Call lvwasx(1348, 1317)
If cboxonderdeel.Text = .Range("a1349") Then Call lvwasx(1393, 1362)
If cboxonderdeel.Text = .Range("a1394") Then Call lvwasx(1438, 1407)
If cboxonderdeel.Text = .Range("a1439") Then Call lvwasx(1483, 1452)
If cboxonderdeel.Text = .Range("a1484") Then Call lvwasx(1528, 1497)
If cboxonderdeel.Text = .Range("a1529") Then Call lvwasx(1573, 1542)
If cboxonderdeel.Text = .Range("a1575") Then Call lvwasx(1619, 1586)
If cboxonderdeel.Text = .Range("a1620") Then Call lvwasx(1664, 1631)
If cboxonderdeel.Text = .Range("a1665") Then Call lvwasx(1709, 1676)
If cboxonderdeel.Text = .Range("a1710") Then Call lvwasx(1754, 1721)
If cboxonderdeel.Text = .Range("a1755") Then Call lvwasx(1799, 1766)
If cboxonderdeel.Text = .Range("a1800") Then Call lvwasx(1844, 1811)
If cboxonderdeel.Text = .Range("a1845") Then Call lvwasx(1889, 1856)
If cboxonderdeel.Text = .Range("a1890") Then Call lvwasx(1934, 1901)
If cboxonderdeel.Text = .Range("a1935") Then Call lvwasx(1979, 1946)
If cboxonderdeel.Text = .Range("a1980") Then Call lvwasx(2024, 1991)
If cboxonderdeel.Text = .Range("a2025") Then Call lvwasx(2069, 2036)
If cboxonderdeel.Text = .Range("a2070") Then Call lvwasx(2114, 2081)
If cboxonderdeel.Text = .Range("a2115") Then Call lvwasx(2159, 2126)
If cboxonderdeel.Text = .Range("a2160") Then Call lvwasx(2204, 2171)
If cboxonderdeel.Text = .Range("a2206") Then Call lvwasx(2250, 2217)
If cboxonderdeel.Text = .Range("a2251") Then Call lvwasx(2295, 2262)
If cboxonderdeel.Text = .Range("a2296") Then Call lvwasx(2340, 2307)
If cboxonderdeel.Text = .Range("a2341") Then Call lvwasx(2385, 2352)
If cboxonderdeel.Text = .Range("a2386") Then Call lvwasx(2430, 2397)
If cboxonderdeel.Text = .Range("a2431") Then Call lvwasx(2475, 2442)
If cboxonderdeel.Text = .Range("a2476") Then Call lvwasx(2520, 2487)
If cboxonderdeel.Text = .Range("a2521") Then Call lvwasx(2565, 2532)
If cboxonderdeel.Text = .Range("a2566") Then Call lvwasx(2610, 2577)
If cboxonderdeel.Text = .Range("a2611") Then Call lvwasx(2655, 2622)
If cboxonderdeel.Text = .Range("a2656") Then Call lvwasx(2700, 2667)
If cboxonderdeel.Text = .Range("a2701") Then Call lvwasx(2745, 2712)
If cboxonderdeel.Text = .Range("a2746") Then Call lvwasx(2790, 2757)
If cboxonderdeel.Text = .Range("a2791") Then Call lvwasx(2835, 2802)
End With
End Sub
Private Sub lvwasx(ByVal cellRow As Long, ByVal firstI As Long)
Dim i As Long
Dim LastRow As Long
Dim RangeAI As Range
Dim Cell As Range
Dim NotEqual As Boolean
LastRow = Worksheets("onderhoud").Range("A" & cellRow).End(xlUp).Row
For i = firstI To LastRow
Set RangeAI = Worksheets("onderhoud").Range("A" & i)
If RangeAI.Value <> "" Then
NotEqual = True
For Each Cell In WSo.Range("D2:D7,E2:E10,F2:F10").Cells
If RangeAI.Value = Cell.Value Then
NotEqual = False
Exit For
End If
Next Cell
If NotEqual Then Me.cboxonderhoud.AddItem RangeAI.Text
End If
Next i
End Sub
The reason I asked about the cboxonderdeel.Text equaling more than one range is that the code is evaluating each If statement, which can take a bit of time. If it will only equal one of the Ranges, then change the 'If cboxonderdeel.Text = .Range...' lines to:
Select Case cboxonderdeel.Text
Case .Range("a2"): Call lvwasx(44, 13)
Case .Range("a45"): Call lvwasx(89, 56)
Case .Range("a90"): Call lvwasx(134, 101)
Case .Range("a135"): Call lvwasx(179, 146)
Case .Range("a180"): Call lvwasx(224, 191)
Case .Range("a225"): Call lvwasx(269, 236)
Case .Range("a270"): Call lvwasx(314, 281)
Case .Range("a315"): Call lvwasx(359, 326)
Case .Range("a360"): Call lvwasx(404, 371)
Case .Range("a405"): Call lvwasx(449, 416)
Case .Range("a450"): Call lvwasx(494, 461)
Case .Range("a495"): Call lvwasx(539, 506)
Case .Range("a540"): Call lvwasx(584, 551)
Case .Range("a585"): Call lvwasx(629, 596)
Case .Range("a630"): Call lvwasx(674, 641)
Case .Range("a675"): Call lvwasx(719, 686)
Case .Range("a720"): Call lvwasx(764, 731)
Case .Range("a765"): Call lvwasx(809, 776)
Case .Range("a811"): Call lvwasx(853, 822)
Case .Range("a854"): Call lvwasx(898, 867)
Case .Range("a899"): Call lvwasx(943, 912)
Case .Range("a944"): Call lvwasx(988, 957)
Case .Range("a989"): Call lvwasx(1033, 1002)
Case .Range("a1034"): Call lvwasx(1078, 1047)
Case .Range("a1079"): Call lvwasx(1123, 1092)
Case .Range("a1124"): Call lvwasx(1168, 1137)
Case .Range("a1169"): Call lvwasx(1213, 1182)
Case .Range("a1214"): Call lvwasx(1258, 1227)
Case .Range("a1259"): Call lvwasx(1303, 1272)
Case .Range("a1304"): Call lvwasx(1348, 1317)
Case .Range("a1349"): Call lvwasx(1393, 1362)
Case .Range("a1394"): Call lvwasx(1438, 1407)
Case .Range("a1439"): Call lvwasx(1483, 1452)
Case .Range("a1484"): Call lvwasx(1528, 1497)
Case .Range("a1529"): Call lvwasx(1573, 1542)
Case .Range("a1575"): Call lvwasx(1619, 1586)
Case .Range("a1620"): Call lvwasx(1664, 1631)
Case .Range("a1665"): Call lvwasx(1709, 1676)
Case .Range("a1710"): Call lvwasx(1754, 1721)
Case .Range("a1755"): Call lvwasx(1799, 1766)
Case .Range("a1800"): Call lvwasx(1844, 1811)
Case .Range("a1845"): Call lvwasx(1889, 1856)
Case .Range("a1890"): Call lvwasx(1934, 1901)
Case .Range("a1935"): Call lvwasx(1979, 1946)
Case .Range("a1980"): Call lvwasx(2024, 1991)
Case .Range("a2025"): Call lvwasx(2069, 2036)
Case .Range("a2070"): Call lvwasx(2114, 2081)
Case .Range("a2115"): Call lvwasx(2159, 2126)
Case .Range("a2160"): Call lvwasx(2204, 2171)
Case .Range("a2206"): Call lvwasx(2250, 2217)
Case .Range("a2251"): Call lvwasx(2295, 2262)
Case .Range("a2296"): Call lvwasx(2340, 2307)
Case .Range("a2341"): Call lvwasx(2385, 2352)
Case .Range("a2386"): Call lvwasx(2430, 2397)
Case .Range("a2431"): Call lvwasx(2475, 2442)
Case .Range("a2476"): Call lvwasx(2520, 2487)
Case .Range("a2521"): Call lvwasx(2565, 2532)
Case .Range("a2566"): Call lvwasx(2610, 2577)
Case .Range("a2611"): Call lvwasx(2655, 2622)
Case .Range("a2656"): Call lvwasx(2700, 2667)
Case .Range("a2701"): Call lvwasx(2745, 2712)
Case .Range("a2746"): Call lvwasx(2790, 2757)
Case .Range("a2791"): Call lvwasx(2835, 2802)
End Select
And you'll notice even more of an improvement in time.
Using the Select Case method will add 2 lines to the total code. But even with that, 98 lines of code total is much less than 2800. :)
Matt
Norie
03-14-2005, 10:04 AM
Does this work? It's totally untested.
Private Sub cboxonderdeel_Change()
Dim arrVals As Variant
Dim valI As Long
Dim valA As Long
Dim I As Long
cboxonderhoud.Clear
With cboxonderhoud
.ColumnCount = 1
.ColumnWidths = 280
.Width = 300
.Height = 15
.ListRows = 17
End With
arrVals = Array(2, 44, 13, 45, 89, 56, 90, 134, 101, 135, 179, 146, 180, 224, 191, _
225, 269, 236, 270, 314, 281, 315, 359, 326, 360, 404, 371, 405, 449, 416, _
450, 494, 461, 495, 539, 506, 540, 584, 551, 585, 629, 596, 630, 674, 641, _
675, 719, 686, 720, 764, 731, 765, 809, 776, 811, 853, 822, 854, 898, 867, _
899, 943, 912, 944, 988, 957, 989, 1033, 1002, 1034, 1078, 1047, _
1079, 1123, 1092, 1124, 1168, 1137, 1169, 1213, 1182, 1214, 1258, 1227, _
1259, 1303, 1272, 1304, 1348, 1317, 1349, 1393, 1362, 1394, 1438, 1407, _
1439, 1483, 1452, 1484, 1528, 1497, 1529, 1573, 1542, 1575, 1619, 1586, _
1620, 1664, 1631, 1665, 1709, 1676, 1710, 1754, 1721, 1755, 1799, 1766, _
1800, 1844, 1811, 1845, 1889, 1856, 1890, 1934, 1901, 1935, 1979, 1946, _
1980, 2024, 1991, 2025, 2069, 2036, 2070, 2114, 2081, 2115, 2159, 2126, _
2160, 2204, 2171, 2206, 2250, 2217, 2251, 2295, 2262, 2296, 2340, 2307, _
2341, 2385, 2352, 2386, 2430, 2397, 2431, 2475, 2442, 2476, 2520, 2487, _
2521, 2565, 2532, 2566, 2610, 2577, 2611, 2655, 2622, 2656, 2700, 2667, _
2701, 2745, 2712, 2746, 2790, 2757, 2791, 2835, 2802)
For I = LBound(arrVals) To UBound(arrVals) Step 3
If cboxonderdeel.Text = Worksheets("onderhoud").Range("A" & arrVals(I)) Then
valA = arrVals(I + 1)
valI = arrVals(I + 2)
Exit For
End If
Next I
If (valA * valI) <> 0 Then
Call lvwasx(valA, valI)
End If
End Sub
Private Sub lvwasx(ByVal cellRow As Long, ByVal firstI As Long)
Dim I As Long
Dim J As Long
Dim LastRow As Long
Dim WSo As Worksheet
Dim boolAdd As Boolean
Dim rng As Range
Dim rngTest As Range
Set WSo = Worksheets("onderhoud")
LastRow = WSo.Range("A" & cellRow).End(xlUp).Row
For I = firstI To LastRow
Set rng = WSo.Range("A" & I)
boolAdd = rng.Value <> ""
If boolAdd Then
Set rngTest = WSo.Range("D2")
For J = 0 To 5
boolAdd = boolAdd And rng.Value <> rngTest.Offset(J, 0).Value
Next J
If boolAdd Then
Set rngTest = WSo.Range("E2")
For J = 0 To 8
boolAdd = boolAdd And rng.Value <> rngTest.Offset(J, 0).Value
Next J
If boolAdd Then
Set rngTest = WSo.Range("F2")
For J = 0 To 8
boolAdd = boolAdd And rng.Value <> rngTest.Offset(J, 0).Value
Next J
End If
End If
End If
If boolAdd Then
Me.cboxonderhoud.AddItem WSo.Range("A" & I).Text
End If
Next I
End Sub
Regouin
03-15-2005, 12:28 AM
Unbelievable, you guys are the best. The call sub routine does compile it only generates heaps of code. cboxonderdeel can only contain 1 range at a time, but it can be changed to a different one afterwards, say if you accidently choose the wrong part. But i fixed that by clearing the cboxonderhoud as the cboxonderdeel changes.
Thanks a lot for all your effort into this I'm going to try it out right away
Ok mvidas, your code works beautifully, I smashing my head against the wall why on earth I didnt think of using parameters, guess I had been blinded by the way I thought it should've worked. Its fast as hell now and it does exactly what I want. Thanx again everyone for your great support.
Hey mvidas, I elaborated on your code with the parameters and was able to eliminate close to 5000 lines of code, still wondering why on earth I didnt think of the parameters earlier, i owe you one. :beerchug:
Paleo
03-15-2005, 06:38 AM
Hey Regouin,
as you have improved even more the code, post it here. This may helps another user with a problem similar to yours.
Regouin
03-15-2005, 07:34 AM
ok, this is the total code, it contains 3 combobox where the second combobox is dependant on what is in the first and the 3rd combobox is dependant on the second and thusfor indirectly dependant on the 1st. It contains 2 buttons, 1 which clears one maintenance task and on that lets you change an entire part (so all the maintenance tasks for the part are reset and the date for the replacement is stored). Hope I can help someone with it.
Option Explicit
Private Sub UserForm_activate()
'eerste combobox vullen met de 4 wasstraten
Dim WasStraat(3, 1)
'grootte combobox specificeren
With cboxwasstraat
.ColumnCount = 1
.ColumnWidths = 75
.Width = 100
.Height = 15
.ListRows = 6
End With
'combobox waardes toewijzen
With Worksheets("onderhoud")
WasStraat(0, 0) = .Range("A1")
WasStraat(1, 0) = .Range("A810")
WasStraat(2, 0) = .Range("A1574")
WasStraat(3, 0) = .Range("A2205")
'waardes in de combobox laden
cboxwasstraat.List() = WasStraat
End With
End Sub
Private Sub cboxwasstraat_Change()
'bij een verandering van wasstraat wordt de
'2de combobox aangepast aan de bijbehorende straat
'2de combobox vullen met de onderdelen uit de bij 1 gekozen wasstraat
With Worksheets("onderhoud")
'uitzoeken welke wasstraat is geselecteerd en doorverwijzen
If cboxwasstraat.Text = .Range("a1") Then GoTo voorwaslinks
If cboxwasstraat.Text = .Range("a810") Then GoTo voorwasrechts
If cboxwasstraat.Text = .Range("a1574") Then GoTo hoofdwas1
If cboxwasstraat.Text = .Range("a2205") Then GoTo hoofdwas2
voorwaslinks:
Dim voorwaslinks(17, 1)
'voor de voorwas links een combobox specificeren
With cboxonderdeel
.ColumnCount = 1
.ColumnWidths = 180
.Width = 200
.Height = 15
.ListRows = 18
End With
With Worksheets("onderhoud")
'onderdelen voor de combobox opgeven
voorwaslinks(0, 0) = .Range("a2")
voorwaslinks(1, 0) = .Range("a45")
voorwaslinks(2, 0) = .Range("a90")
voorwaslinks(3, 0) = .Range("a135")
voorwaslinks(4, 0) = .Range("a180")
voorwaslinks(5, 0) = .Range("a225")
voorwaslinks(6, 0) = .Range("a270")
voorwaslinks(7, 0) = .Range("a315")
voorwaslinks(8, 0) = .Range("a360")
voorwaslinks(9, 0) = .Range("a405")
voorwaslinks(10, 0) = .Range("a450")
voorwaslinks(11, 0) = .Range("a495")
voorwaslinks(12, 0) = .Range("a540")
voorwaslinks(13, 0) = .Range("a585")
voorwaslinks(14, 0) = .Range("a630")
voorwaslinks(15, 0) = .Range("a675")
voorwaslinks(16, 0) = .Range("a720")
voorwaslinks(17, 0) = .Range("a765")
'lijst in de combobox laden
cboxonderdeel.List() = voorwaslinks
End With
GoTo eind
voorwasrechts:
'zelfde voor rechter voorwas
Dim voorwasrechts(16, 1)
With cboxonderdeel
.ColumnCount = 1
.ColumnWidths = 180
.Width = 200
.Height = 15
.ListRows = 17
End With
With Worksheets("onderhoud")
voorwasrechts(0, 0) = .Range("a811")
voorwasrechts(1, 0) = .Range("a854")
voorwasrechts(2, 0) = .Range("a899")
voorwasrechts(3, 0) = .Range("a944")
voorwasrechts(4, 0) = .Range("a989")
voorwasrechts(5, 0) = .Range("a1034")
voorwasrechts(6, 0) = .Range("a1079")
voorwasrechts(7, 0) = .Range("a1124")
voorwasrechts(8, 0) = .Range("a1169")
voorwasrechts(9, 0) = .Range("a1214")
voorwasrechts(10, 0) = .Range("a1259")
voorwasrechts(11, 0) = .Range("a1304")
voorwasrechts(12, 0) = .Range("a1349")
voorwasrechts(13, 0) = .Range("a1394")
voorwasrechts(14, 0) = .Range("a1439")
voorwasrechts(15, 0) = .Range("a1484")
voorwasrechts(16, 0) = .Range("a1529")
cboxonderdeel.List() = voorwasrechts
End With
GoTo eind
hoofdwas1:
'zelfde voor hoofdwas 1
Dim hoofdwas1(13, 1)
With cboxonderdeel
.ColumnCount = 1
.ColumnWidths = 180
.Width = 200
.Height = 15
.ListRows = 17
End With
With Worksheets("onderhoud")
hoofdwas1(0, 0) = .Range("a1575")
hoofdwas1(1, 0) = .Range("a1620")
hoofdwas1(2, 0) = .Range("a1665")
hoofdwas1(3, 0) = .Range("a1710")
hoofdwas1(4, 0) = .Range("a1755")
hoofdwas1(5, 0) = .Range("a1800")
hoofdwas1(6, 0) = .Range("a1845")
hoofdwas1(7, 0) = .Range("a1890")
hoofdwas1(8, 0) = .Range("a1935")
hoofdwas1(9, 0) = .Range("a1980")
hoofdwas1(10, 0) = .Range("a2025")
hoofdwas1(11, 0) = .Range("a2070")
hoofdwas1(12, 0) = .Range("a2115")
hoofdwas1(13, 0) = .Range("a2160")
cboxonderdeel.List() = hoofdwas1
End With
GoTo eind
hoofdwas2:
'zelfde voor hoofdwas 2
Dim hoofdwas2(13, 1)
With cboxonderdeel
.ColumnCount = 1
.ColumnWidths = 180
.Width = 200
.Height = 15
.ListRows = 17
End With
With Worksheets("onderhoud")
hoofdwas2(0, 0) = .Range("a2206")
hoofdwas2(1, 0) = .Range("a2251")
hoofdwas2(2, 0) = .Range("a2296")
hoofdwas2(3, 0) = .Range("a2341")
hoofdwas2(4, 0) = .Range("a2386")
hoofdwas2(5, 0) = .Range("a2431")
hoofdwas2(6, 0) = .Range("a2476")
hoofdwas2(7, 0) = .Range("a2521")
hoofdwas2(8, 0) = .Range("a2566")
hoofdwas2(9, 0) = .Range("a2611")
hoofdwas2(10, 0) = .Range("a2656")
hoofdwas2(11, 0) = .Range("a2701")
hoofdwas2(12, 0) = .Range("a2746")
hoofdwas2(13, 0) = .Range("a2791")
cboxonderdeel.List() = hoofdwas2
End With
GoTo eind
eind:
End With
End Sub
Private Sub cboxonderdeel_Change()
'vullen van de onderhoudsbox met onderhoudstaken
cboxonderhoud.Clear
With cboxonderhoud
.ColumnCount = 1
.ColumnWidths = 280
.Width = 300
.Height = 15
.ListRows = 17
End With
'wasstraat opnieuw bepalen in verband met dubbele namen onderdelen
With Worksheets("onderhoud")
If cboxwasstraat.Text = .Range("a1") Then GoTo 1
If cboxwasstraat.Text = .Range("a810") Then GoTo 2
If cboxwasstraat.Text = .Range("a1574") Then GoTo 3
If cboxwasstraat.Text = .Range("a2205") Then GoTo 4
'Onderhoudstaken voor onderdeel bij elkaar zoeken voor
'linker voorwas, rechter voorwas, hoofdwas1 en hoofdwas2
1:
Select Case cboxonderdeel.Text
Case .Range("a2"): Call lvwasx(44, 13)
Case .Range("a45"): Call lvwasx(89, 56)
Case .Range("a90"): Call lvwasx(134, 101)
Case .Range("a135"): Call lvwasx(179, 146)
Case .Range("a180"): Call lvwasx(224, 191)
Case .Range("a225"): Call lvwasx(269, 236)
Case .Range("a270"): Call lvwasx(314, 281)
Case .Range("a315"): Call lvwasx(359, 326)
Case .Range("a360"): Call lvwasx(404, 371)
Case .Range("a405"): Call lvwasx(449, 416)
Case .Range("a450"): Call lvwasx(494, 461)
Case .Range("a495"): Call lvwasx(539, 506)
Case .Range("a540"): Call lvwasx(584, 551)
Case .Range("a585"): Call lvwasx(629, 596)
Case .Range("a630"): Call lvwasx(674, 641)
Case .Range("a675"): Call lvwasx(719, 686)
Case .Range("a720"): Call lvwasx(764, 731)
Case .Range("a765"): Call lvwasx(809, 776)
Case Else: Call fout
End Select
GoTo eind
2:
Select Case cboxonderdeel.Text
Case .Range("a811"): Call lvwasx(853, 822)
Case .Range("a854"): Call lvwasx(898, 867)
Case .Range("a899"): Call lvwasx(943, 912)
Case .Range("a944"): Call lvwasx(988, 957)
Case .Range("a989"): Call lvwasx(1033, 1002)
Case .Range("a1034"): Call lvwasx(1078, 1047)
Case .Range("a1079"): Call lvwasx(1123, 1092)
Case .Range("a1124"): Call lvwasx(1168, 1137)
Case .Range("a1169"): Call lvwasx(1213, 1182)
Case .Range("a1214"): Call lvwasx(1258, 1227)
Case .Range("a1259"): Call lvwasx(1303, 1272)
Case .Range("a1304"): Call lvwasx(1348, 1317)
Case .Range("a1349"): Call lvwasx(1393, 1362)
Case .Range("a1394"): Call lvwasx(1438, 1407)
Case .Range("a1439"): Call lvwasx(1483, 1452)
Case .Range("a1484"): Call lvwasx(1528, 1497)
Case .Range("a1529"): Call lvwasx(1573, 1542)
Case Else: Call fout
End Select
GoTo eind
3:
Select Case cboxonderdeel.Text
Case .Range("a1575"): Call lvwasx(1619, 1586)
Case .Range("a1620"): Call lvwasx(1664, 1631)
Case .Range("a1665"): Call lvwasx(1709, 1676)
Case .Range("a1710"): Call lvwasx(1754, 1721)
Case .Range("a1755"): Call lvwasx(1799, 1766)
Case .Range("a1800"): Call lvwasx(1844, 1811)
Case .Range("a1845"): Call lvwasx(1889, 1856)
Case .Range("a1890"): Call lvwasx(1934, 1901)
Case .Range("a1935"): Call lvwasx(1979, 1946)
Case .Range("a1980"): Call lvwasx(2024, 1991)
Case .Range("a2025"): Call lvwasx(2069, 2036)
Case .Range("a2070"): Call lvwasx(2114, 2081)
Case .Range("a2115"): Call lvwasx(2159, 2126)
Case .Range("a2160"): Call lvwasx(2204, 2171)
Case Else: Call fout
End Select
GoTo eind
4:
Select Case cboxonderdeel.Text
Case .Range("a2206"): Call lvwasx(2250, 2217)
Case .Range("a2251"): Call lvwasx(2295, 2262)
Case .Range("a2296"): Call lvwasx(2340, 2307)
Case .Range("a2341"): Call lvwasx(2385, 2352)
Case .Range("a2386"): Call lvwasx(2430, 2397)
Case .Range("a2431"): Call lvwasx(2475, 2442)
Case .Range("a2476"): Call lvwasx(2520, 2487)
Case .Range("a2521"): Call lvwasx(2565, 2532)
Case .Range("a2566"): Call lvwasx(2610, 2577)
Case .Range("a2611"): Call lvwasx(2655, 2622)
Case .Range("a2656"): Call lvwasx(2700, 2667)
Case .Range("a2701"): Call lvwasx(2745, 2712)
Case .Range("a2746"): Call lvwasx(2790, 2757)
Case .Range("a2791"): Call lvwasx(2835, 2802)
Case Else: Call fout
End Select
eind:
End With
End Sub
Private Sub lvwasx(ByVal cellRow As Long, ByVal firstI As Long)
Dim i As Long
Dim LastRow As Long
Dim RangeAI As Range
Dim cell As Range
Dim NotEqual As Boolean
LastRow = Worksheets("onderhoud").Range("A" & cellRow).End(xlUp).Row
For i = firstI To LastRow
Set RangeAI = Worksheets("onderhoud").Range("A" & i)
If RangeAI.Value <> "" Then
NotEqual = True
For Each cell In Worksheets("onderhoud").Range( _
"D2:D13,E2:E13,F2:F13").Cells
If RangeAI.Value = cell.Value Then
NotEqual = False
Exit For
End If
Next cell
If NotEqual Then Me.cboxonderhoud.AddItem RangeAI.Text
End If
Next i
End Sub
Private Sub cmdinvoer_Click()
'invoeren uitgevoerde onderhoudstaak
Dim aantal As String
'om bevestiging vragen voor het uitvoeren en dus vervangen van gegevens
If MsgBox(strExcelApp & "De volgende onderhoudstaak " & _
"het " & cboxonderhoud.Text & " van de " & cboxonderdeel.Text & _
" in de " & cboxwasstraat.Text & " gaat nu verwerkt worden, weet " & _
"U zeker dat U door wilt gaan?", vbYesNo + vbQuestion) = vbNo Then
Exit Sub
End If
'wegschrijven onderhoudshistorie in de rij van de onderhoudstaak
With Sheets("onderhoud").Range("A:A")
Row = .Find(What:=cboxwasstraat.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
End With
Select Case Row
Case "1": Call was(809, 1)
Case "810": Call was(1573, 810)
Case "1574": Call was(2204, 1574)
Case "2205": Call was(2840, 2205)
Case Else: Call fout
End Select
End Sub
Private Sub was(ByVal Ihoog As Long, ByVal Ilaag As Long)
Dim begin As Range
Dim eind As Range
Set begin = Worksheets("onderhoud").Range("A" & Ilaag)
Set eind = Worksheets("onderhoud").Range("A" & Ihoog)
With Sheets("onderhoud").Range(begin, eind)
Row = .Find(What:=cboxonderdeel.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
End With
Select Case Row
Case "2": Call odeel(44, 3)
Case "45": Call odeel(89, 46)
Case "90": Call odeel(134, 91)
Case "135": Call odeel(179, 136)
Case "180": Call odeel(224, 181)
Case "225": Call odeel(269, 226)
Case "270": Call odeel(314, 271)
Case "315": Call odeel(359, 316)
Case "360": Call odeel(404, 361)
Case "405": Call odeel(449, 406)
Case "450": Call odeel(494, 451)
Case "495": Call odeel(539, 496)
Case "540": Call odeel(584, 541)
Case "585": Call odeel(629, 586)
Case "630": Call odeel(674, 631)
Case "675": Call odeel(719, 676)
Case "720": Call odeel(764, 721)
Case "765": Call odeel(809, 766)
Case "811": Call odeel(853, 812)
Case "854": Call odeel(898, 855)
Case "899": Call odeel(943, 900)
Case "944": Call odeel(988, 945)
Case "989": Call odeel(1033, 990)
Case "1034": Call odeel(1078, 1035)
Case "1079": Call odeel(1123, 1080)
Case "1124": Call odeel(1168, 1125)
Case "1169": Call odeel(1213, 1170)
Case "1214": Call odeel(1258, 1215)
Case "1259": Call odeel(1303, 1260)
Case "1304": Call odeel(1348, 1305)
Case "1349": Call odeel(1393, 1350)
Case "1394": Call odeel(1438, 1395)
Case "1439": Call odeel(1483, 1440)
Case "1484": Call odeel(1528, 1485)
Case "1529": Call odeel(1573, 1530)
Case "1575": Call odeel(1619, 1576)
Case "1620": Call odeel(1664, 1621)
Case "1665": Call odeel(1709, 1666)
Case "1710": Call odeel(1754, 1711)
Case "1755": Call odeel(1799, 1756)
Case "1800": Call odeel(1844, 1801)
Case "1845": Call odeel(1889, 1846)
Case "1890": Call odeel(1934, 1891)
Case "1935": Call odeel(1979, 1936)
Case "1980": Call odeel(2024, 1981)
Case "2025": Call odeel(2069, 2026)
Case "2070": Call odeel(2114, 2071)
Case "2115": Call odeel(2159, 2116)
Case "2160": Call odeel(2204, 2161)
Case "2206": Call odeel(2250, 2207)
Case "2251": Call odeel(2295, 2252)
Case "2296": Call odeel(2340, 2297)
Case "2341": Call odeel(2385, 2342)
Case "2386": Call odeel(2430, 2387)
Case "2431": Call odeel(2475, 2432)
Case "2476": Call odeel(2520, 2477)
Case "2521": Call odeel(2565, 2522)
Case "2566": Call odeel(2610, 2567)
Case "2611": Call odeel(2655, 2612)
Case "2656": Call odeel(2700, 2657)
Case "2701": Call odeel(2745, 2702)
Case "2746": Call odeel(2790, 2747)
Case "2791": Call odeel(2850, 2792)
Case Else: Call fout
End Select
End Sub
Private Sub odeel(ByVal Ihg As Long, ByVal Ilg As Long)
Dim aantal As Long
Dim datum As Date
Dim begin As Range
Dim eind As Range
Set begin = Worksheets("onderhoud").Range("A" & Ilg)
Set eind = Worksheets("onderhoud").Range("A" & Ihg)
With Sheets("onderhoud").Range(begin, eind)
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
End With
datum = Date
If cboxwasstraat.Text = Worksheets("onderhoud").Range("a1") _
Then aantal = Worksheets("hoofd").Range("b5")
If cboxwasstraat.Text = Worksheets("onderhoud").Range("a810") _
Then aantal = Worksheets("hoofd").Range("c5")
If cboxwasstraat.Text = Worksheets("onderhoud").Range("a1574") _
Then aantal = Worksheets("hoofd").Range("d5")
If cboxwasstraat.Text = Worksheets("onderhoud").Range("a2205") _
Then aantal = Worksheets("hoofd").Range("e5")
Dim locdat As Range
Dim locaan As Range
Set locdat = Worksheets("onderhoud").Range("B" & Row)
Set locaan = Worksheets("onderhoud").Range("C" & Row)
locdat.FormulaR1C1 = datum
locaan.FormulaR1C1 = aantal
Dim i As Integer
Dim cell As Range
Dim r1k1 As String
i = 8
Do
i = i + 1
r1k1 = "R" & Row & "C" & i
conv = R1C1converter(r1k1)
Loop Until Worksheets("onderhoud").Range(conv) = ""
Worksheets("onderhoud").Range(conv) = Date
i = i + 1
r1k1 = "R" & Row & "C" & i
conv = R1C1converter(r1k1)
Worksheets("onderhoud").Range(conv) = aantal
MsgBox ("Onderhoudstaak verwerkt, voer de volgende verrichte " & _
"onderhoudstaak in of sluit af")
End Sub
Private Sub fout()
MsgBox ("Foutieve invoer, controleer invoer en probeer opnieuw.")
End Sub
Private Sub cmdvervang_click()
'vervangen van een onderdeel
Dim aantal As String
If MsgBox(strExcelApp & "U heeft de " & cboxonderdeel.Text & _
" in de " & cboxwasstraat.Text & " vervangen, weet U zeker dat U" & _
" dit wilt invoeren?", vbYesNo + vbQuestion) = vbNo Then
Exit Sub
End If
With Sheets("onderhoud").Range("A:A")
Row = .Find(What:=cboxwasstraat.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
End With
Select Case Row
Case "1": Call wasverv(809, 1)
Case "810": Call wasverv(1573, 810)
Case "1574": Call wasverv(2204, 1574)
Case "2205": Call wasverv(2840, 2205)
Case Else: Call fout
End Select
End Sub
Private Sub wasverv(ByVal Ihoog As Long, ByVal Ilaag As Long)
Dim begin As Range
Dim eind As Range
Set begin = Worksheets("onderhoud").Range("A" & Ilaag)
Set eind = Worksheets("onderhoud").Range("A" & Ihoog)
With Sheets("onderhoud").Range(begin, eind)
Row = .Find(What:=cboxonderdeel.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
End With
Select Case Row
Case "2": Call vervond(44, 13)
Case "45": Call vervond(89, 56)
Case "90": Call vervond(134, 101)
Case "135": Call vervond(179, 146)
Case "180": Call vervond(224, 191)
Case "225": Call vervond(269, 236)
Case "270": Call vervond(314, 281)
Case "315": Call vervond(359, 326)
Case "360": Call vervond(404, 371)
Case "405": Call vervond(449, 416)
Case "450": Call vervond(494, 461)
Case "495": Call vervond(539, 506)
Case "540": Call vervond(584, 551)
Case "585": Call vervond(629, 596)
Case "630": Call vervond(674, 641)
Case "675": Call vervond(719, 686)
Case "720": Call vervond(764, 731)
Case "765": Call vervond(809, 776)
Case "811": Call vervond(853, 822)
Case "854": Call vervond(898, 867)
Case "899": Call vervond(943, 912)
Case "944": Call vervond(988, 957)
Case "989": Call vervond(1033, 1002)
Case "1034": Call vervond(1078, 1047)
Case "1079": Call vervond(1123, 1092)
Case "1124": Call vervond(1168, 1137)
Case "1169": Call vervond(1213, 1182)
Case "1214": Call vervond(1258, 1227)
Case "1259": Call vervond(1303, 1272)
Case "1304": Call vervond(1348, 1317)
Case "1349": Call vervond(1393, 1362)
Case "1394": Call vervond(1438, 1407)
Case "1439": Call vervond(1483, 1452)
Case "1484": Call vervond(1528, 1497)
Case "1529": Call vervond(1573, 1542)
Case "1575": Call vervond(1619, 1586)
Case "1620": Call vervond(1664, 1631)
Case "1665": Call vervond(1709, 1676)
Case "1710": Call vervond(1754, 1721)
Case "1755": Call vervond(1799, 1766)
Case "1800": Call vervond(1844, 1811)
Case "1845": Call vervond(1889, 1856)
Case "1890": Call vervond(1934, 1901)
Case "1935": Call vervond(1979, 1946)
Case "1980": Call vervond(2024, 1991)
Case "2025": Call vervond(2069, 2036)
Case "2070": Call vervond(2114, 2081)
Case "2115": Call vervond(2159, 2126)
Case "2160": Call vervond(2204, 2171)
Case "2206": Call vervond(2250, 2217)
Case "2251": Call vervond(2295, 2262)
Case "2296": Call vervond(2340, 2307)
Case "2341": Call vervond(2385, 2352)
Case "2386": Call vervond(2430, 2397)
Case "2431": Call vervond(2475, 2442)
Case "2476": Call vervond(2520, 2487)
Case "2521": Call vervond(2565, 2532)
Case "2566": Call vervond(2610, 2577)
Case "2611": Call vervond(2655, 2622)
Case "2656": Call vervond(2700, 2667)
Case "2701": Call vervond(2745, 2712)
Case "2746": Call vervond(2790, 2757)
Case "2791": Call vervond(2835, 2802)
Case Else: Call fout
End Select
End Sub
Private Sub vervond(ByVal cellRow As Long, ByVal firstI As Long)
Dim i As Long
Dim LastRow As Long
Dim RangeAI As Range
Dim cell As Range
Dim NotEqual As Boolean
Dim aantal As Long
Dim datum As Date
Dim vervdatum As Long
datum = Date
If cboxwasstraat.Text = Worksheets("onderhoud").Range("a1") _
Then aantal = Worksheets("hoofd").Range("b5")
If cboxwasstraat.Text = Worksheets("onderhoud").Range("a810") _
Then aantal = Worksheets("hoofd").Range("c5")
If cboxwasstraat.Text = Worksheets("onderhoud").Range("a1574") _
Then aantal = Worksheets("hoofd").Range("d5")
If cboxwasstraat.Text = Worksheets("onderhoud").Range("a2205") _
Then aantal = Worksheets("hoofd").Range("e5")
vervdatum = firstI - 5
Set locvervdatum = Worksheets("onderhoud").Range("B" & vervdatum)
vervaantal = firstI - 4
Set locvervaantal = Worksheets("onderhoud").Range("B" & vervaantal)
locvervdatum.FormulaR1C1 = datum
locvervaantal.FormulaR1C1 = aantal
Call ohoudhistorie(aantal, datum, vervdatum, vervaantal)
LastRow = Worksheets("onderhoud").Range("A" & cellRow).End(xlUp).Row
For i = firstI To LastRow
Set RangeAI = Worksheets("onderhoud").Range("A" & i)
If RangeAI.Value <> "" Then
NotEqual = True
For Each cell In Worksheets("onderhoud").Range( _
"D2:D13,E2:E13,F2:F13").Cells
If RangeAI.Value = cell.Value Then
NotEqual = False
Exit For
End If
Next cell
If NotEqual Then Call vervanging(i, firstI)
End If
Next i
MsgBox ("De " & cboxonderdeel.Text & " in de " & cboxwasstraat.Text & _
" is vervangen, de tellerstand is weer op 0 gesteld en de " & _
"installatiedatum is " & Date & ".")
End Sub
Private Sub vervanging(ByVal rij As Long, ByVal begin As Long)
Dim datum As Date
Dim locdat As Range
Dim locaan As Range
Dim rijaantal As Long
Dim aantal As Long
datum = Date
rijaantal = begin - 3
aantal = Worksheets("hoofd").Range("b" & rijaantal)
Set locdat = Worksheets("onderhoud").Range("B" & rij)
Set locaan = Worksheets("onderhoud").Range("C" & rij)
locdat.FormulaR1C1 = datum
locaan.FormulaR1C1 = aantal
End Sub
Private Sub ohoudhistorie(ByVal aantal As Long, ByVal datum As Date, _
ByVal rijdatum As Long, ByVal rijaantal As Long)
Dim i As Integer
Dim cell As Range
Dim r1k1 As String
i = 2
Do
i = i + 1
r1k1 = "R" & rijdatum & "C" & i
conv = R1C1converter(r1k1)
Loop Until Worksheets("onderhoud").Range(conv) = ""
Worksheets("onderhoud").Range(conv) = datum
i = 2
Do
i = i + 1
r1k1 = "R" & rijaantal & "C" & i
conv = R1C1converter(r1k1)
Loop Until Worksheets("onderhoud").Range(conv) = ""
Worksheets("onderhoud").Range(conv) = aantal
End Sub
Private Sub cmdafsluit_Click()
Unload Me
Worksheets("hoofd").Activate
End Sub
I used the R1C1converter that can be found in the KB for the storage of the maintenance tasks since that is the easiest way to translate columns into numbers.
thanks everyone for making this possible :)
Paleo
03-15-2005, 07:42 AM
Great, thanks for providing the final code.
johnske
03-15-2005, 02:40 PM
Hi Regouin,
To make life a little easier still... When, as in your example, you have a very large number of ranges that have to be referred to, there's a lot less typing involved for you if you use shorthand notation. This means instead of writing Range("A1") you can just write [A1].
Here's an example using a bit of code from above
Select Case cboxonderdeel.Text
Case .Range("a2"): Call lvwasx(44, 13)
Case .Range("a45"): Call lvwasx(89, 56)
Case .Range("a90"): Call lvwasx(134, 101)
Case .Range("a135"): Call lvwasx(179, 146)
Case .Range("a180"): Call lvwasx(224, 191)
...etc
Select Case cboxonderdeel.Text
Case .[a2]: Call lvwasx(44, 13)
Case .[a45]: Call lvwasx(89, 56)
Case .[a90]: Call lvwasx(134, 101)
Case .[a135]: Call lvwasx(179, 146)
Case .[a180]: Call lvwasx(224, 191)
..etc
Regards,
John
Regouin
03-16-2005, 12:11 AM
allright, didnt know that, thanks. But since most of the range work is copy paste anyway it is not so much less typing work but it looks tidier.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.