Userform find, edit and update data error

haroon2015 · Apr 9th 2014

hello
i m using a userform to find data, edit data and update data.....when i click
CommandButton2 to update data an error is comming

Quote

Run-time error '13':

Type mismatch

Code

Private Sub ComboBox1_Change()
    Dim v As Variant
    Dim r As Range
    Set r = Sheet4.Range("D61:D500")
    v = Application.Match(CDbl(ComboBox1.Value), r, False)
    If Not IsError(v) Then
        TextBox1.Value = r.Cells(v).Offset(0, -1).Value
        TextBox2.Value = r.Cells(v).Offset(0, -3).Value
        TextBox3.Value = r.Cells(v).Offset(0, 1).Value
        TextBox4.Value = r.Cells(v).Offset(0, 2).Value
        TextBox5.Value = r.Cells(v).Offset(0, 3).Value
        TextBox6.Value = r.Cells(v).Offset(0, 4).Value
        'MsgBox "Now you can edit."
    End If
End Sub

Display More

Code

Private Sub CommandButton2_Click()
Dim v As Variant
    Dim r As Range
    Set r = Sheet4.Range("D61:D500")
    v = Application.Match(CDbl(ComboBox1.Value), r, False)
    If Not IsError(v) Then
r.Cells(v).Offset(0, -1).Value = TextBox1.Value
r.Cells(v).Offset(0, -3).Value = TextBox2.Value
r.Cells(v).Offset(0, 1).Value = TextBox3.Value
r.Cells(v).Offset(0, 2).Value = TextBox4.Value
r.Cells(v).Offset(0, 3).Value = TextBox5.Value
r.Cells(v).Offset(0, 4).Value = TextBox6.Value
 TextBox1.Value = ""
 TextBox2.Value = ""
 TextBox3.Value = ""
 TextBox4.Value = ""
 TextBox5.Value = ""
 TextBox6.Value = ""
 ComboBox1.Value = ""
 MsgBox "successful edited."
 'Exit Sub
'Else
'End If
'Else
'Exit Sub
End If
'Next
End Sub

Display More

need help
thanx

haroon2015 · Apr 9th 2014

Re: Userform find, edit and update data error

ups!
sort out thanx alot

solved

**royUK** · Apr 9th 2014

Re: Userform find, edit and update data error

You might take the time to share the answert in case it helps others with a similar problem.

haroon2015 · Apr 9th 2014

Re: Userform find, edit and update data error

ok royUK

haroon2015 · Apr 9th 2014

Re: Userform find, edit and update data error

here is right code to work Solved By Sixthsense

Code

Private Sub ComboBox1_Change()Dim v As Variant, r As Range, vValue As Variant, i As ByteSet r = Sheet4.Range("D61:D500")vValue = ComboBox1.ValueIf IsNumeric(vValue) Then vValue = CDbl(vValue)v = Application.Match(vValue, r, False)    If Not IsError(v) Then        TextBox1.Value = r.Cells(v).Offset(0, -1).Value        TextBox2.Value = r.Cells(v).Offset(0, -3).Value        TextBox3.Value = r.Cells(v).Offset(0, 0).Value        TextBox4.Value = r.Cells(v).Offset(0, 1).Value        TextBox5.Value = r.Cells(v).Offset(0, 2).Value        TextBox6.Value = r.Cells(v).Offset(0, 3).Value        'MsgBox "Now you can edit."    End IfEnd Sub

Code

Private Sub CommandButton2_Click()Dim v As Variant, r As Range, vValue As Variant, i As ByteSet r = Sheet4.Range("D61:D500")vValue = ComboBox1.ValueIf IsNumeric(vValue) Then vValue = CDbl(vValue)v = Application.Match(vValue, r, False)If IsNumeric(v) Then    With r.Cells(v)        .Offset(, -1).Value = TextBox1.Value        .Offset(, -3).Value = TextBox2.Value        .Offset(, 0).Value = TextBox3.Value        .Offset(, 1).Value = TextBox4.Value        .Offset(, 2).Value = TextBox5.Value        .Offset(, 3).Value = TextBox6.Value    End With             For i = 1 To 6        Controls("TextBox" & i).Value = ""    Next i        ComboBox1.Value = ""    'MsgBox "successful edited."End IfEnd Sub

Userform find, edit and update data error

Participate now!

Share