Hallo ik zit met een probleem.
Ik wou een registratie formulier maken waarbij de dagdelen worden verwerkt en de bedrag zichtbaar komt wat de werknemer vergoed krijgt.
Nu doet het formulier zelf maken met address plaats en naam wel, maar het invoeren van de dagdelen doet het niet.
De regel waar excel moeilijk doet is Cells(FoundRange2.Row, naamreg.Column).Select
**Zie hieronder voor de script**
Ik wou een registratie formulier maken waarbij de dagdelen worden verwerkt en de bedrag zichtbaar komt wat de werknemer vergoed krijgt.
Nu doet het formulier zelf maken met address plaats en naam wel, maar het invoeren van de dagdelen doet het niet.
De regel waar excel moeilijk doet is Cells(FoundRange2.Row, naamreg.Column).Select
**Zie hieronder voor de script**
Code:
Private Sub CommandButton1_Click()
If Trim(ComboBox1.Text) = "" Then
MsgBox "Voer eerst de Naam in."
ElseIf Trim(ComboBox2.Text) = "" Then
MsgBox "Voer minimaal 1 datum in."
Else
Set naam = Sheets("Gegevens").Cells.Find(What:=ComboBox1.Value, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
Set naamreg = Sheets("Registratie").Cells.Find(What:=ComboBox1.Value, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
Set FoundRange = Sheets("Gegevens").Cells.Find(What:=ComboBox1.Value, LookIn:=xlFormulas, LookAt:=xlWhole)
Sheets("Formulier").Select
Range("A16:G38,F5:H7,H12,B12:C12,E12:F12").Select
Selection.ClearContents
Sheets("Gegevens").Select
Range("B" & FoundRange.Row).Select
Selection.Copy
Sheets("Formulier").Select
Range("G5").Select
ActiveSheet.Paste
Sheets("Gegevens").Select
Range("C" & FoundRange.Row).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Formulier").Select
Range("G7").Select
ActiveSheet.Paste
Sheets("Gegevens").Select
Range("D" & FoundRange.Row).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Formulier").Select
Range("F6").Select
ActiveSheet.Paste
Sheets("Gegevens").Select
Range("E" & FoundRange.Row).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Formulier").Select
Range("F7").Select
ActiveSheet.Paste
Sheets("Gegevens").Select
Range("A" & FoundRange.Row).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Formulier").Select
Range("B12").Select
ActiveSheet.Paste
If Trim(ComboBox2.Text) = "" Then
'Nothing
Else
Range("G16").Select
ActiveCell.FormulaR1C1 = "=RC[-3]*RC[-1]"
Range("A16").Value = ComboBox2.Value
Sheets("Gegevens").Select
Range("H" & FoundRange.Row).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Formulier").Select
Range("D16").Select
ActiveSheet.Paste
Sheets("Registratie").Select
Set FoundRange2 = Sheets("Registratie").Cells.Find(What:=ComboBox2.Value, LookIn:=xlFormulas, LookAt:=xlWhole)
Cells(FoundRange2.Row, naamreg.Column).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Formulier").Select
Range("F16").Select
ActiveSheet.Paste
Range("D16,G16").Select
Selection.NumberFormat = _
"_([$€-x-euro2] * #,##0.00_);_([$€-x-euro2] * (#,##0.00);_([$€-x-euro2] * ""-""??_);_(@_)"
End If
If Trim(ComboBox3.Text) = "" Then
'Nothing
Else
Range("G17").Select
ActiveCell.FormulaR1C1 = "=RC[-3]*RC[-1]"
Range("A17").Value = ComboBox3.Value
Sheets("Gegevens").Select
Range("H" & FoundRange.Row).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Formulier").Select
Range("D17").Select
ActiveSheet.Paste
Sheets("Registratie").Select
Set FoundRange3 = Sheets("Registratie").Cells.Find(What:=ComboBox3.Value, LookIn:=xlFormulas, LookAt:=xlWhole)
Cells(FoundRange3.Row, naamreg.Column).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Formulier").Select
Range("F17").Select
ActiveSheet.Paste
Range("D17,G17").Select
Selection.NumberFormat = _
"_([$€-x-euro2] * #,##0.00_);_([$€-x-euro2] * (#,##0.00);_([$€-x-euro2] * ""-""??_);_(@_)"
End If
If Trim(ComboBox4.Text) = "" Then
'Nothing
Else
Range("G18").Select
ActiveCell.FormulaR1C1 = "=RC[-3]*RC[-1]"
Range("A18").Value = ComboBox4.Value
Sheets("Gegevens").Select
Range("H" & FoundRange.Row).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Formulier").Select
Range("D18").Select
ActiveSheet.Paste
Sheets("Registratie").Select
Set FoundRange4 = Sheets("Registratie").Cells.Find(What:=ComboBox4.Value, LookIn:=xlFormulas, LookAt:=xlWhole)
Cells(FoundRange4.Row, naamreg.Column).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Formulier").Select
Range("F18").Select
ActiveSheet.Paste
Range("D18,G18").Select
Selection.NumberFormat = _
"_([$€-x-euro2] * #,##0.00_);_([$€-x-euro2] * (#,##0.00);_([$€-x-euro2] * ""-""??_);_(@_)"
End If
If Trim(ComboBox5.Text) = "" Then
'Nothing
Else
Range("G19").Select
ActiveCell.FormulaR1C1 = "=RC[-3]*RC[-1]"
Range("A19").Value = ComboBox5.Value
Sheets("Gegevens").Select
Range("H" & FoundRange.Row).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Formulier").Select
Range("D19").Select
ActiveSheet.Paste
Sheets("Registratie").Select
Set FoundRange5 = Sheets("Registratie").Cells.Find(What:=ComboBox5.Value, LookIn:=xlFormulas, LookAt:=xlWhole)
Cells(FoundRange5.Row, naamreg.Column).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Formulier").Select
Range("F19").Select
ActiveSheet.Paste
Range("D19,G19").Select
Selection.NumberFormat = _
"_([$€-x-euro2] * #,##0.00_);_([$€-x-euro2] * (#,##0.00);_([$€-x-euro2] * ""-""??_);_(@_)"
End If
If Trim(ComboBox6.Text) = "" Then
'Nothing
Else
Range("G20").Select
ActiveCell.FormulaR1C1 = "=RC[-3]*RC[-1]"
Range("A20").Value = ComboBox6.Value
Sheets("Gegevens").Select
Range("H" & FoundRange.Row).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Formulier").Select
Range("D20").Select
ActiveSheet.Paste
Sheets("Registratie").Select
Set FoundRange6 = Sheets("Registratie").Cells.Find(What:=ComboBox6.Value, LookIn:=xlFormulas, LookAt:=xlWhole)
Cells(FoundRange6.Row, naamreg.Column).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Formulier").Select
Range("F20").Select
ActiveSheet.Paste
Range("D20,G20").Select
Selection.NumberFormat = _
"_([$€-x-euro2] * #,##0.00_);_([$€-x-euro2] * (#,##0.00);_([$€-x-euro2] * ""-""??_);_(@_)"
End If
If Trim(ComboBox7.Text) = "" Then
'Nothing
Else
Range("G21").Select
ActiveCell.FormulaR1C1 = "=RC[-3]*RC[-1]"
Range("A21").Value = ComboBox7.Value
Sheets("Gegevens").Select
Range("H" & FoundRange.Row).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Formulier").Select
Range("D21").Select
ActiveSheet.Paste
Sheets("Registratie").Select
Set FoundRange7 = Sheets("Registratie").Cells.Find(What:=ComboBox7.Value, LookIn:=xlFormulas, LookAt:=xlWhole)
Cells(FoundRange7.Row, naamreg.Column).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Formulier").Select
Range("F21").Select
ActiveSheet.Paste
Range("D21,G21").Select
Selection.NumberFormat = _
"_([$€-x-euro2] * #,##0.00_);_([$€-x-euro2] * (#,##0.00);_([$€-x-euro2] * ""-""??_);_(@_)"
End If
If Trim(ComboBox8.Text) = "" Then
'Nothing
Else
Range("G22").Select
ActiveCell.FormulaR1C1 = "=RC[-3]*RC[-1]"
Range("A22").Value = ComboBox8.Value
Sheets("Gegevens").Select
Range("H" & FoundRange.Row).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Formulier").Select
Range("D22").Select
ActiveSheet.Paste
Sheets("Registratie").Select
Set FoundRange8 = Sheets("Registratie").Cells.Find(What:=ComboBox8.Value, LookIn:=xlFormulas, LookAt:=xlWhole)
Cells(FoundRange8.Row, naamreg.Column).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Formulier").Select
Range("F22").Select
ActiveSheet.Paste
Range("D22,G22").Select
Selection.NumberFormat = _
"_([$€-x-euro2] * #,##0.00_);_([$€-x-euro2] * (#,##0.00);_([$€-x-euro2] * ""-""??_);_(@_)"
End If
If Trim(ComboBox9.Text) = "" Then
'Nothing
Else
Range("G23").Select
ActiveCell.FormulaR1C1 = "=RC[-3]*RC[-1]"
Range("A23").Value = ComboBox9.Value
Sheets("Gegevens").Select
Range("H" & FoundRange.Row).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Formulier").Select
Range("D23").Select
ActiveSheet.Paste
Sheets("Registratie").Select
Set FoundRange9 = Sheets("Registratie").Cells.Find(What:=ComboBox9.Value, LookIn:=xlFormulas, LookAt:=xlWhole)
Cells(FoundRange2.Row, naamreg.Column).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Formulier").Select
Range("F23").Select
ActiveSheet.Paste
Range("D23,G23").Select
Selection.NumberFormat = _
"_([$€-x-euro2] * #,##0.00_);_([$€-x-euro2] * (#,##0.00);_([$€-x-euro2] * ""-""??_);_(@_)"
End If
Range("G47").Select
Selection.NumberFormat = _
"_([$€-x-euro2] * #,##0.00_);_([$€-x-euro2] * (#,##0.00);_([$€-x-euro2] * ""-""??_);_(@_)"
Range("H12").Value = Date
Range("F16:F40").Select
Selection.Replace What:="V", Replacement:="0", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="Z", Replacement:="0", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Unload Me
End If
End Sub
Laatst bewerkt door een moderator: