NessPJ
Active Member
- Joined
- May 10, 2011
- Messages
- 418
- Office Version
- 365
Hi all,
I have a public variable in my routine called "AantalPallets" which can vary from 1 to 5.
I am now stepping in the code/routine that i pasted at the end of this post.
If the value is 3 for example:
I would like to execute the steps for Batch1, THT1, SSCC1 through xxx3 but not for xxx4 and xxx5.
Same goes for putting the values to a cell (which follows after that in the posted routine).
I would like to enter/process the values for Batch1, THT1, SSCC1 through xxx3 but not for xxx4 and xxx5.
Is there a way i should do this using an IF block, an Array for each of the xxx1, xxx2, xxx3 variables? or should i make this a For Loop ?
I'm a bit puzzled what the best approach would be.
I have a public variable in my routine called "AantalPallets" which can vary from 1 to 5.
I am now stepping in the code/routine that i pasted at the end of this post.
If the value is 3 for example:
I would like to execute the steps for Batch1, THT1, SSCC1 through xxx3 but not for xxx4 and xxx5.
Same goes for putting the values to a cell (which follows after that in the posted routine).
I would like to enter/process the values for Batch1, THT1, SSCC1 through xxx3 but not for xxx4 and xxx5.
Is there a way i should do this using an IF block, an Array for each of the xxx1, xxx2, xxx3 variables? or should i make this a For Loop ?
I'm a bit puzzled what the best approach would be.
VBA Code:
Public Batch1 As String, THT1 As String, SSCC1 As String, Batch2 As String, THT2 As String, SSCC2 As String, Batch3 As String, THT3 As String, SSCC3 As String, Batch4 As String, THT4 As String, SSCC4 As String, Batch5 As String, THT5 As String, SSCC5 As String
Public SSCCVerplicht As String
Batch1 = Sheets("Menu").Range("H5").Value
THT1 = Sheets("Menu").Range("H6").Value
SSCC1 = Sheets("Menu").Range("H7").Value
Batch2 = Sheets("Menu").Range("H9").Value
THT2 = Sheets("Menu").Range("H10").Value
SSCC2 = Sheets("Menu").Range("H11").Value
Batch3 = Sheets("Menu").Range("H13").Value
THT3 = Sheets("Menu").Range("H14").Value
SSCC3 = Sheets("Menu").Range("H15").Value
Batch4 = Sheets("Menu").Range("K5").Value
THT4 = Sheets("Menu").Range("K6").Value
SSCC4 = Sheets("Menu").Range("K7").Value
Batch5 = Sheets("Menu").Range("K9").Value
THT5 = Sheets("Menu").Range("K10").Value
SSCC5 = Sheets("Menu").Range("K11").Value
If SSCCVerplicht = "J" Then
GoTo SSCCVerplichtJa
Else
GoTo SSCCVerplichtNee
End If
SSCCVerplichtJa: 'Controle of alle vereiste data wel is ingevuld
If Batch1 = vbNullString Or THT1 = vbNullString Or SSCC1 = vbNullString Or _
Batch2 = vbNullString Or THT2 = vbNullString Or SSCC2 = vbNullString Or _
Batch3 = vbNullString Or THT3 = vbNullString Or SSCC3 = vbNullString Or _
Batch4 = vbNullString Or THT4 = vbNullString Or SSCC4 = vbNullString Or _
Batch5 = vbNullString Or THT5 = vbNullString Or SSCC5 = vbNullString _
Then
GoTo EindeLeeg
Else
GoTo DataOkay
End If
Exit Sub
SSCCVerplichtNee: 'Controle of alle vereiste data wel is ingevuld (zonder SSCC)
If Batch1 = vbNullString Or THT1 = vbNullString Or _
Batch2 = vbNullString Or THT2 = vbNullString Or _
Batch3 = vbNullString Or THT3 = vbNullString Or _
Batch4 = vbNullString Or THT4 = vbNullString Or _
Batch5 = vbNullString Or THT5 = vbNullString _
Then
GoTo EindeLeeg
Else
GoTo DataOkay
End If
Exit Sub
'Wegschrijven van de gegevens
DataOkay:
DatumTijd = Format(Now, "dd-mm-yy hh:mm")
Sheets("Registratie").Cells(RegistratieLR, 2).Value = DatumTijd
Sheets("Registratie").Cells(RegistratieLR, 2).NumberFormat = "DD-MM-YY hh:mm"
Sheets("Registratie").Cells(RegistratieLR, 3).Value = Usernr
Sheets("Registratie").Cells(RegistratieLR, 4).Value = Batch1 'Pallet 1
Sheets("Registratie").Cells(RegistratieLR, 5).NumberFormat = "@"
Sheets("Registratie").Cells(RegistratieLR, 5).Value = THT1
Sheets("Registratie").Cells(RegistratieLR, 6).NumberFormat = "@"
Sheets("Registratie").Cells(RegistratieLR, 6).Value = SSCC1
Sheets("Registratie").Cells(RegistratieLR, 7).Value = Batch2 'Pallet 2
Sheets("Registratie").Cells(RegistratieLR, 8).NumberFormat = "@"
Sheets("Registratie").Cells(RegistratieLR, 8).Value = THT2
Sheets("Registratie").Cells(RegistratieLR, 9).NumberFormat = "@"
Sheets("Registratie").Cells(RegistratieLR, 9).Value = SSCC2
Sheets("Registratie").Cells(RegistratieLR, 10).Value = Batch3 'Pallet 3
Sheets("Registratie").Cells(RegistratieLR, 11).NumberFormat = "@"
Sheets("Registratie").Cells(RegistratieLR, 11).Value = THT3
Sheets("Registratie").Cells(RegistratieLR, 12).NumberFormat = "@"
Sheets("Registratie").Cells(RegistratieLR, 12).Value = SSCC3
Sheets("Registratie").Cells(RegistratieLR, 13).Value = Batch4 'Pallet 4
Sheets("Registratie").Cells(RegistratieLR, 14).NumberFormat = "@"
Sheets("Registratie").Cells(RegistratieLR, 14).Value = THT4
Sheets("Registratie").Cells(RegistratieLR, 15).NumberFormat = "@"
Sheets("Registratie").Cells(RegistratieLR, 15).Value = SSCC4
Sheets("Registratie").Cells(RegistratieLR, 16).Value = Batch5 'Pallet 5
Sheets("Registratie").Cells(RegistratieLR, 17).NumberFormat = "@"
Sheets("Registratie").Cells(RegistratieLR, 17).Value = THT5
Sheets("Registratie").Cells(RegistratieLR, 18).NumberFormat = "@"
Sheets("Registratie").Cells(RegistratieLR, 18).Value = SSCC5