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

 

[Fluff]

Try replacing this

[COLOR=#333333]rowselect = Me.txtsearchreportno.Value[/COLOR]

with

   rowselect = Application.Match(txtsearchreportno, Range("A:A"), 0)
   If IsError(rowselect) Then
      MsgBox txtsearchreportno, vbExclamation, "No Match Found"
      Exit Sub
   End If

 

[bala04msw]

Dear Yongle,

I had applied your code as your suggested in Post #7 . But no message box has come and the same type mismatch error - Run-time error 13 only comes. I do not know.. what to do? really making me terrible.

Can i share the file here? what is the option? please explain.. i will share the file

 

[bala04msw]

Dear Mr. Fluff,

Still same issue, no result and progress. How can i fix this, can i share the file here? what is the process

 

[Fluff]

You cannot upload files to this site.
You'll need to upload to a share site such as OneDrive, DropBox, GoogleDrive, mark for sharing & post the link to the thread

 

[bala04msw]

You cannot upload files to this site.
You'll need to upload to a share site such as OneDrive, DropBox, GoogleDrive, mark for sharing & post the link to the thread

Dear Mr. Fluff,
Instead correcting this code, i would like to describe my requirement, hope you will help me on this.
Data Entry User Form:
1. I have developed an user form (SVmainForm) to do a data entry
2. SVmainForm includes, Text boxes, combo boxes and option button (Radio button) to input the data.
3. The entered data stores in excel worksheet (SVReport)


Edit User Form
1. I have developed one more user form ("SVeditForm" as same as "SVmainForm" to edit and save / update data with out any changes in the data input tool such as Text box, combo boxes and option button.
2. I want to recall the data from excel worksheet exactly in the form of SVmainForm.
3. Main variable is Report Number and it is in text box (Example format of Report Number: 2007201878PU) to recall the data from excel worksheet. (SVmainForm: txtreportno and SVeditForm: txtsearchreportno)
4. My requirement is i want to edit the data and save the data in the same row of the excel worksheet.


Kindly do the needful

Regards

 

[bala04msw]

Dear Fluff,

https://drive.google.com/open?id=1aRyloHVwTtGkpgg-5uahVfK45K9-oig_

Thanks for guiding me on this. Please do needful.

Regards

 

[Fluff]

It would help if you had supplied the password

 

[bala04msw]

It would help if you had supplied the password

Dear Fluff,

I am extremely sorry. password is "saathii123"

 

[Fluff]

Not sure what the problem is, as the change I suggested works fine.

 

[bala04msw]

Not sure what the problem is, as the change I suggested works fine.

Dear Fluff,

Is it working for you? or else .. you are also facing same kind of issue?