vba - Count number of Ampersands and Exclamations in Excel text row by row -

vba - Count number of Ampersands and Exclamations in Excel text row by row -

i need help on how count number of ampersands , exclamations in text in excel. able first row value not updating. here code have far. give thanks you.

sub display_readability_scores() dim stats variant dim words string dim characters string dim paragraphs string dim sentences string dim sentences_per_paragraph string dim words_per_sentence string dim characters_per_word string dim ratio_of_passive_sentences string dim flesch_reading_ease_score string dim flesch_kincaid_grade_level_score string dim coleman_liau_readability_score string dim activedocument object dim row integer dim column integer dim amp string row = 2 set activedocument = createobject("word.document") while worksheets("sheet1").cells(row, 1) <> "" activedocument.content = worksheets("sheet1").cells(row, 2) column = 3 words = activedocument.content.readabilitystatistics(1).value characters = activedocument.content.readabilitystatistics(2).value paragraphs = activedocument.content.readabilitystatistics(3).value sentences = activedocument.content.readabilitystatistics(4).value sentences_per_paragraph = activedocument.content.readabilitystatistics(5).value words_per_sentence = activedocument.content.readabilitystatistics(6).value characters_per_word = activedocument.content.readabilitystatistics(7).value ratio_of_passive_sentences = activedocument.content.readabilitystatistics(8).value flesch_reading_ease_score = activedocument.content.readabilitystatistics(9).value flesch_kincaid_grade_level_score = activedocument.content.readabilitystatistics(10).value amp = application.worksheetfunction.countif("worksheets("sheet1").cells(row, 2), "&") each stats in worksheets("sheet1").cells(row, 2) worksheets("sheet1").cells(row, column) = words column = column + 1 worksheets("sheet1").cells(row, column) = characters column = column + 1 worksheets("sheet1").cells(row, column) = paragraphs column = column + 1 worksheets("sheet1").cells(row, column) = sentences column = column + 1 worksheets("sheet1").cells(row, column) = sentences_per_paragraph column = column + 1 worksheets("sheet1").cells(row, column) = words_per_sentence column = column + 1 worksheets("sheet1").cells(row, column) = characters_per_word column = column + 1 worksheets("sheet1").cells(row, column) = ratio_of_passive_sentences column = column + 1 worksheets("sheet1").cells(row, column) = flesch_reading_ease_score column = column + 1 worksheets("sheet1").cells(row, column) = flesch_kincaid_grade_level_score column = column + 1 worksheets("sheet1").cells(row, column) = amp row = row + 1 next stats loop end sub

sub display_readability_scores() dim stats variant dim words string dim characters string dim paragraphs string dim sentences string dim sentences_per_paragraph string dim words_per_sentence string dim characters_per_word string dim ratio_of_passive_sentences string dim flesch_reading_ease_score string dim flesch_kincaid_grade_level_score string dim coleman_liau_readability_score string dim activedocument object dim row integer dim column integer dim amp long, ex long dim rs object, txt string row = 2 set activedocument = createobject("word.document") while worksheets("sheet1").cells(row, 1) <> "" txt = worksheets("sheet1").cells(row, 2).value activedocument.content = txt set rs = activedocument.content.readabilitystatistics words = rs(1).value characters = rs(2).value paragraphs = rs(3).value sentences = rs(4).value sentences_per_paragraph = rs(5).value words_per_sentence = rs(6).value characters_per_word = rs(7).value ratio_of_passive_sentences = rs(8).value flesch_reading_ease_score = rs(9).value flesch_kincaid_grade_level_score = rs(10).value amp = countchar(txt, "&") ex = countchar(txt, "!") worksheets("sheet1").cells(row, 3).resize(1, 12).value = _ array(words, characters, paragraphs, sentences, sentences_per_paragraph, _ words_per_sentence, characters_per_word, ratio_of_passive_sentences, _ flesch_reading_ease_score, flesch_kincaid_grade_level_score, amp, ex) row = row + 1 loop end sub function countchar(txt string, char string) long countchar = len(txt) - len(replace(txt, char, "")) end function

vba