Hulp nodig met VBA fout code 91

Potuijt · 26 sep 2018

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**

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

edmoor · 26 sep 2018

Plaats eerst je code in codetags en met inspringpunten. Zo gaat niemand het lezen.
Zie mijn handtekening.

SjonR · 26 sep 2018

Waarschijnlijk kan de waarde niet worden gevonden......

Hulp nodig met VBA fout code 91

Potuijt

Nieuwe gebruiker

edmoor

Inventaris

SjonR

Verenigingslid

Nieuwste berichten

Wij waarderen jouw privacy