Facing Runtime Error - 13, Type of Mismatch

[bala04msw]

Dear Experts,

This is duplicate thread originally posted in VBA express forum and link is http://www.vbaexpress.com/forum/sho...Type-Mismatch-quot-while-edit-and-update-user

Kindly help me to resolve this issue.

I have developed user form for data entry purposes and its works fine with store data in excel worksheet. Now, i have added the edit option to update the data, if any changes. Here i am facing two issues and as follows:


1. In the edit code: There is a field called Time in and Time out, While i retrieve the data, time is coming as "0.25" instead of "06.00"
2. After edit the user form, i want to update the corrected data in the same row of the excel worksheet. But i am facing Run-time error '13' Type mismatch, while update the changing. especially in this code of line (rowselect = rowselect + 1)

Data is retrieved to edit based on Report Number: "2907201845PU" (Example)

I have used the following code for edit:

[COLOR=#333333]Private Sub btnSVedit_Click()
 Dim r As Variant
        With Sheets("SVReport")
        r = Application.Match(txtsearchreportno, .Range("A:A"), 0)
        If IsError(r) Then
            MsgBox txtsearchreportno, vbExclamation, "No Match Found"
        Else
            cbtown.Text = .Range("B" & r).Value
            cbdistrict.Text = .Range("C" & r).Value
            cbstate.Text = .Range("D" & r).Value
            cbfacilityname.Text = .Range("E" & r).Value
            txttypeofsite.Text = .Range("F" & r).Value
            txttypeoffacility.Text = .Range("G" & r).Value
            txtsvetanasiteid.Text = .Range("H" & r).Value
            txtsimsid.Text = .Range("I" & r).Value
            txthivpulseid.Text = .Range("J" & r).Value
            txtdate.Text = .Range("K" & r).Value
            txtreportno.Text = .Range("A" & r).Value
            txttimein.Text = .Range("L" & r).Value
            txttimeout.Text = .Range("M" & r).Value
            cbsvdonebyname.Text = .Range("N" & r).Value
            cbsvdonebydesignation.Text = .Range("O" & r).Value
            txtsvothers.Text = .Range("P" & r).Value
            obdoctor.Value = .Range("W" & r).Value
            'etc.
        End If
    End With
End Sub[/COLOR]

I have used the following code for update:

[COLOR=#333333]Private Sub btnupdate_Click()
If Me.txtsearchreportno.Value = "" Then
MsgBox "Report No. Can Not be Blank!!!", vbExclamation, "Report No"
Exit Sub
End If
ReportNo = Me.txtsearchreportno.Value
Sheets("SVReport").Select
Dim rowselect As Long
Dim msg As String
Dim ans As String
rowselect = Me.txtsearchreportno.Value
rowselect = rowselect + 1
Rows(rowselect).Select
Cells(rowselect, 1) = Me.textreportno.Value
Cells(rowselect, 2) = Me.cbtown.Value
Cells(rowselect, 3) = Me.cbdistrict.Value
Cells(rowselect, 4) = Me.cbstate.Value
Cells(rowselect, 5) = Me.cbfacilityname.Value
Cells(rowselect, 6) = Me.txttypeofsite.Value
Cells(rowselect, 7) = Me.txttypeoffacility.Value
Cells(rowselect, 8) = Me.txtsvetanasiteid.Value
Cells(rowselect, 9) = Me.txtsimsid.Value
Cells(rowselect, 10) = Me.txthivpulseid.Value
Cells(rowselect, 11) = Me.txtdate.Value
Cells(rowselect, 12) = Me.txttimein.Value
Cells(rowselect, 13) = Me.txttimeout.Value
Cells(rowselect, 14) = Me.cbsvdonebyname.Value
Cells(rowselect, 15) = Me.cbsvdonebydesignation.Value
Cells(rowselect, 16) = Me.txtsvothers.Value
Cells(rowselect, 23) = Me.obdoctor.Value
rowselect = rowselect - 1
msg = "Sl No " & rowselect & "  Successfully Updated...Continue?"
Unload Me
ans = MsgBox(msg, vbYesNo, "Update")
If ans = vbYes Then
SVeditForm.Show
Else
Sheets("SVReport").Select
End If
End Sub[/COLOR]

Kindly do the needful


Regards

 

[Yongle]

Try these lines instead

Q1

txttimeout.Text = Format(.Range("M" & r).Value, "HH:MM")

Q2

rowselect = CInt(Me.txtsearchreportno.Value)

 

[bala04msw]

Try these lines instead

Q1

txttimeout.Text = Format(.Range("M" & r).Value, "HH:MM")

Q2

rowselect = CInt(Me.txtsearchreportno.Value)

Dear Mr. Yongle,

The same issue persisting still. runtime error 13 - type mismatch

F8 - detection on "rowselect = CInt(Me.txtsearchreportno.Value)". KIndly guide me to rectify this.

Regards

 

[Yongle]

Did txttimeout.Text display correctly?

 

[Fluff]

You need to find the report number before you update the sheet, in the same manor that you did in the edit sub

 

[bala04msw]

Did txttimeout.Text display correctly?

Yes Yongle, thats perfect. I am struggling with this type mismatch error only.

 

[Yongle]

Insert this line

MsgBox "Report NO = " & vbCr & ReportNo

Below

ReportNo = Me.txtsearchreportno.Value

and run the code
Is the value correct

 

[bala04msw]

Mr. Yongle,

Same issue still. the debug at "rowselect = txtsearchreportno.Value". did you read Mr. Fluff suggestion, i am able to understand his suggestion, but do not know, how should be coded? Could you please help me on this?

 

[bala04msw]

Dear Mr. Fluff,

Thanks for lead suggestion and i am able to understand your suggestion, but do not know, how should be coded? Could you please help me on this?

 

[Yongle]

Did my message box appear when you ran the code as amended in post#7?

Me.txtsearchreportno is in your code
Is there a textbox named txtsearchreportno on your userform?
Does it contain a value?