Unable to get match property in WorksheetFunction class Error!

[butterboxbaby14]

I have created this userform to be used for auditing cases. I have attached a sample. I have the form working for the data entry, however, when using the modify/edit function in pulling up existing cases it gives me the "Unable to get match properly in worksheet function class" error. I have used the same string of command before and it worked on a different sheet.

>>> use code - tags <<<

Private Sub Command_Search_Click()

Dim iRow As Integer

iRow = Application.WorksheetFunction.Match(text_Search, Sheets("Data").Range("G2:G1000"), 0)
   
MsgBox iRow                                   'just to check if the above works but it does not

End Sub

It worked for me from the past not sure what is wrong with it? Please help.

Also, in the actual userform, I would like to have the Score Achieved to display the current score being taken by the agent and it should be interactive. Example, if he get a 5 on the first attribute with a weight of 5, Score Achieved would display 100%. And it goes along as you go down each attribute, would this be possible?

Thanks to whoever can help me.

 

[Hui]

Change your code to use the following

Private Sub Command_Search_Click()

Dim iRow As Integer

iRow = Sheets("Data").Range("G2:G1000").Find(text_Search).Row - 1
  
MsgBox iRow

End Sub

 

[Hui]

As for the second question, Yes
Pls see the attached

 

[butterboxbaby14]

Many thanks Sir for your expertise.The score works perfectly, however, how would I show it on the actual percentage score?

Also, regarding the application of the search i tried to input all the scripts coz what should happen is, after searching for the case number is should pop-up the entry form with all the details of the case for editting. I tried to replace it but it does not work.

Please see below:

Code:

>>> 2nd time --- use code - tags <<<

Private Sub cmdSearch_Click()

    Dim iRow As Integer
  
    iRow = Sheets("Data").Range("G2:G1000").Find(txtSearch).Row - 1

    Unload Data_Query

    Data_Entry.txtAgent = Sheets("Database").Range("Start_Point").Offset(iRow, 1).Value
    Data_Entry.cmbInteraction = Sheets("Database").Range("Start_Point").Offset(iRow, 2).Value
    Data_Entry.txtIDate = Sheets("Database").Range("Start_Point").Offset(iRow, 3).Value
    Data_Entry.cmbZone = Sheets("Database").Range("Start_Point").Offset(iRow, 4).Value
    Data_Entry.cmbCountry = Sheets("Database").Range("Start_Point").Offset(iRow, 5).Value
    Data_Entry.txtCase = Sheets("Database").Range("Start_Point").Offset(iRow, 6).Value
    Data_Entry.txtAssessor = Sheets("Database").Range("Start_Point").Offset(iRow, 9).Value
    Data_Entry.cmbAccount = Sheets("Database").Range("Start_Point").Offset(iRow, 10).Value
    Data_Entry.txtAccount = Sheets("Database").Range("Start_Point").Offset(iRow, 11).Value
    Data_Entry.cmbContact = Sheets("Database").Range("Start_Point").Offset(iRow, 12).Value
    Data_Entry.txtContact = Sheets("Database").Range("Start_Point").Offset(iRow, 13).Value
    Data_Entry.cmbRelated = Sheets("Database").Range("Start_Point").Offset(iRow, 14).Value
    Data_Entry.txtRelated = Sheets("Database").Range("Start_Point").Offset(iRow, 15).Value
    Data_Entry.cmbSubject = Sheets("Database").Range("Start_Point").Offset(iRow, 16).Value
    Data_Entry.txtSubject = Sheets("Database").Range("Start_Point").Offset(iRow, 17).Value
    Data_Entry.cmbCategory = Sheets("Database").Range("Start_Point").Offset(iRow, 18).Value
    Data_Entry.txtCategory = Sheets("Database").Range("Start_Point").Offset(iRow, 19).Value
    Data_Entry.cmbReason = Sheets("Database").Range("Start_Point").Offset(iRow, 20).Value
    Data_Entry.txtReason = Sheets("Database").Range("Start_Point").Offset(iRow, 21).Value
    Data_Entry.cmbEmail = Sheets("Database").Range("Start_Point").Offset(iRow, 22).Value
    Data_Entry.txtEmail = Sheets("Database").Range("Start_Point").Offset(iRow, 23).Value
    Data_Entry.cmbStatus = Sheets("Database").Range("Start_Point").Offset(iRow, 24).Value
    Data_Entry.txtStatus = Sheets("Database").Range("Start_Point").Offset(iRow, 25).Value
    Data_Entry.cmbRequest = Sheets("Database").Range("Start_Point").Offset(iRow, 26).Value
    Data_Entry.txtRequest = Sheets("Database").Range("Start_Point").Offset(iRow, 27).Value
    Data_Entry.cmbAnswer = Sheets("Database").Range("Start_Point").Offset(iRow, 28).Value
    Data_Entry.txtAnswer = Sheets("Database").Range("Start_Point").Offset(iRow, 29).Value
    Data_Entry.cmbLevel = Sheets("Database").Range("Start_Point").Offset(iRow, 30).Value
    Data_Entry.txtLevel = Sheets("Database").Range("Start_Point").Offset(iRow, 31).Value
    Data_Entry.cmbRelatedC = Sheets("Database").Range("Start_Point").Offset(iRow, 32).Value
    Data_Entry.txtRelatedC = Sheets("Database").Range("Start_Point").Offset(iRow, 33).Value
    Data_Entry.cmbAction = Sheets("Database").Range("Start_Point").Offset(iRow, 34).Value
    Data_Entry.txtAction = Sheets("Database").Range("Start_Point").Offset(iRow, 35).Value

    Data_Entry.Show
    Exit Sub

End Sub

Not sure if a different set of codes should be used for that.

Thanks again sir in advance

 

[Hui]

Look at my changes

you can work out what to change from there

 

[butterboxbaby14]

Hi Sir, sorry about the tagging i was not sure how to put it. The query already worked and was able to show the entry form displaying the data that needs to be edited.

So only displaying the score in percentage is what I am trying to figure out now.

Big thanks for your help. Cheers sir!!!

Stay safe

 

[Hui]

Did you look at the Sub Add_Scores() ?

The line whole sub is new, I added it
The final line puts the answer in

Entry_Form.Score_Achieved.Value = CStr(score) & " / " & CStr(weight)


So change that to suit your needs

To get percentage use:

Entry_Form.Score_Achieved.Value = CStr(100 * score / weight)
or
Entry_Form.Score_Achieved.Value = Format(score / weight, "#.0%")

You will learn more by looking, reading and trying than by simply applying an answer from someone else

 

[butterboxbaby14]

Hi Sir,

I did try to research and choose which formula works for me.

I tried using Entry_Form.Score_Achieved.Value = Format(score / weight, "#.0%"), it does display the score, but whenever I try to Reset the form, it gives a Runtime Error 6: Overflow. It refers to the formula above.

Been trying to play around even on the main form can't seem to resolve this on my own. I tried to add Score_Achieved.Value = "" on the Reset Private Sub and it still does not work.

Been googling on how to resolve this but can't seem to find any answer.

I hope you can still help me on this one.

Thanks.

 

[Hui]

You can’t divide by 0 in
Entry_Form.Score_Achieved.Value = Format(score / weight, "#.0%")

so use

If weight = 0 then
   Entry_Form.Score_Achieved.Value = Format(0, "#.0%")
else
   Entry_Form.Score_Achieved.Value = Format(score / weight, "#.0%")
end if

 

[butterboxbaby14]

Thank you very very much sir for the help. It is 100% working now. I will take note of all these codes and learn how to create them. Again, thank you so much. Stay safe and stay healthy sir. God Bless.