What's wrong with my code?

[Jlombard_]

Hi guys,

I've been building some code for a spreadsheet which has a whole host of individual weighted words.

When a user types in a particular sentence into the (Sheet: tester) "Search_box1" field, upon clicking a button I want each word in "search_box1" to be referenced in a separate sheet called "weighting" (Column A3:A) - in column H there is a score for each word.


When the words have been found in the system I want them to be added together to form an overall grade of the sentence which will be displayed in a sheet called 'tester' (CELL REF: GRADE_VALUE).


At the same time, once the button has been clicked I'd want a copy of both the GRADE_VALUE and the Search_box1 cell logged into a sheet called 'history_search'. cells A2 onwards (GRADE_VALUE) and B2 onwards (Search_box1).

Currently, with the code I'm facing a mismatch but I'm not sure what the problem actually is (being new to things like this).

Image of code and error;https://drive.google.com/file/d/1keaJ_VwO-yCbCe1Tq73go0AP7bXEpqdo/view?usp=sharing

Link to a similar sheet; https://drive.google.com/file/d/19z6ArPzzRQ2oSqnkFQ2LeTjcL3b5irLI/view

 

[steve the fish]

Put code in code tags not an image. Im sure not going to type it out myself nor would many others i expect. What immediately looks wrong is you are matching on column A then using the match on on an index that also uses column A. That cant be right. If you are getting a mismatch then weight will need to be a number so change it to long.

 

[MARK858]

Can you copy and paste the code directly in the thread please then select the code and click the # icon to put code tags around it.

Edit: Oops steve the fish already got got there sorry.

 

[Jlombard_]

Put code in code tags not an image. Im sure not going to type it out myself nor would many others i expect. What immediately looks wrong is you are matching on column A then using the match on on an index that also uses column A. That cant be right. If you are getting a mismatch then weight will need to be a number so change it to long.

Public Sub test_subject05()




Dim inputString As String: inputString = "This is an example sentence"
    
    Dim score As Long
    
    ' This splits the input string into words (here we assume they are only separated
    ' by spaces, you'd have to modify this to take punctuations into account).
    Dim words() As String: words = Split(inputString, " ")
    
    ' Loop through all the words we got
    Dim word As Variant
    For Each word In words
        ' Find where in the list our word is
        Dim match As Variant: match = Application.match(LCase$(word), word_weight.Range("A2:A40000"), 0)
        Dim weight As Variant
        
        If IsError(match) Then
        weight = 0 ' word is not in weight sheet, do something
    Else
        ' Lookup word score using index we got earlier
        weight = Application.WorksheetFunction.Index(word_weight.Range("A2:A100000"), match)
        If IsError(weight) Then weight = 0
    End If
     
       
        ' Add the weight to the total sentence score
        score = score + weight
    Next
    
    MsgBox "Sentence grade is: " & score
End Sub

 

[Jlombard_]

Can you copy and paste the code directly in the thread please then select the code and click the # icon to put code tags around it.

Edit: Oops steve the fish already got got there sorry.

Public Sub test_subject05()




Dim inputString As String: inputString = "This is an example sentence"
    
    Dim score As Long
    
    ' This splits the input string into words (here we assume they are only separated
    ' by spaces, you'd have to modify this to take punctuations into account).
    Dim words() As String: words = Split(inputString, " ")
    
    ' Loop through all the words we got
    Dim word As Variant
    For Each word In words
        ' Find where in the list our word is
        Dim match As Variant: match = Application.match(LCase$(word), word_weight.Range("A2:A40000"), 0)
        Dim weight As Variant
        
        If IsError(match) Then
        weight = 0 ' word is not in weight sheet, do something
    Else
        ' Lookup word score using index we got earlier
        weight = Application.WorksheetFunction.Index(word_weight.Range("A2:A100000"), match)
        If IsError(weight) Then weight = 0
    End If
     
       
        ' Add the weight to the total sentence score
        score = score + weight
    Next
    
    MsgBox "Sentence grade is: " & score
End Sub

 

[steve the fish]

Ok so what about what else i said? Did you alter it?

 

[Jlombard_]

Ok so what about what else i said? Did you alter it?

just to make sure I'm clear on what you said;

1) Move things from column A on the index (Have done so to column B)

2) "If you are getting a mismatch then weight will need to be a number so change it to long." - this bit I don't quite understand (quite new to a lot of this). Are you saying change it from "Dim weight As Variant" to " Dim weight As long?" - if so that still produced the same error.

 

[Fluff]

You need to change score from long to double

    Dim score As Double

And you also need to remove the worksheetfunction from the index line & change the column you're looking at

        weight = Application.Index(word_weight.Range("h2:h100000"), match)

PS Leave weight as Variant

 

[Jlombard_]

You need to change score from long to double

    Dim score As Double

And you also need to remove the worksheetfunction from the index line & change the column you're looking at

        weight = Application.Index(word_weight.Range("h2:h100000"), match)

PS Leave weight as Variant

You're my hero!

Instead of the pop-up msgbox if I wanted the grade to be displayed as a 1 decimal value (ie 6.5 as opposed to 6.5443452) and for it to be pulled into a merged cell called "grade" on the "tester" sheet what would I need to change?

 

[Jlombard_]

Although I have just noticed that upon clicking the cmd button the score is always the same regardless of the values i enter into the search_box1 field?