• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Unable to get match property in WorksheetFunction class Error!

butterboxbaby14

New Member
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 <<<
Code:
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.
 

Attachments

Last edited by a moderator:

Hui

Excel Ninja
Staff member
Change your code to use the following

Code:
Private Sub Command_Search_Click()

Dim iRow As Integer

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

End Sub
 

butterboxbaby14

New Member
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 <<<

Code:
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 :)
 
Last edited by a moderator:

butterboxbaby14

New Member
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

Excel Ninja
Staff member
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

New Member
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.
 

Attachments

Hui

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

so use
Code:
If weight = 0 then
   Entry_Form.Score_Achieved.Value = Format(0, "#.0%")
else
   Entry_Form.Score_Achieved.Value = Format(score / weight, "#.0%")
end if
 
Last edited:

butterboxbaby14

New Member
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.
 
Top