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

 

[Fluff]

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?

That's because you have this at the top of the sub

 inputString = "This is an example sentence"

So you are only ever testing that string

 

[Jlombard_]

That's because you have this at the top of the sub

 inputString = "This is an example sentence"

So you are only ever testing that string

I didn't even notice that! - would i need to delete he "this is an example sentence" or copy across every sentence/word?

 

[Fluff]

Change it to

inputString = Sheet3.Range("I15").Value

and change the msgbox to

    MsgBox "Sentence grade is: " & Format(score, "#0.00")

 

[Jlombard_]

Change it to

inputString = Sheet3.Range("I15").Value

and change the msgbox to

    MsgBox "Sentence grade is: " & Format(score, "#0.00")

fluff, do you accept marriage proposals? you're a genius!

If I wanted the score value to appear in a cell called "grade" on the "tester" sheet, instead of or as well as the msg box, how would I make that happen?

 

[Fluff]

Something like

sheet3.range("H16").value=score

 

[Jlombard_]

Something like

sheet3.range("H16").value=score

Thank you so much! you've been amazingly helpful with all of this! Been working on this project for around 6 weeks now and thanks to you I believe it's all complete minus a few minor issues

 

[Fluff]

Glad to help & thanks for the feedback

 

[Jlombard_]

Glad to help & thanks for the feedback

I do have a couple of questions if you wouldn't mind helping me a little more?

Within the same button I'd want the data tested to pull through to a sheet called 'history_search' with column A being the subject grade produced and column B being the subject tested. In the past i've used the offset method linked within database formula (=COUNTA(History_search!A2:A999)) to find the bottom entry and then add the entry in - however this was done with a userform. When it comes to copying content across I'm not entirely sure on the best practice on how to carry the information across - any suggestions?

 

[Fluff]

Try adding this to the end of the code

    Sheet4.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 2).Value = Array(Sheet3.Range("I15").Value, score)