Using Excel to manipulate vocabulary dataset

[johnodocs]

Hi everyone

Difficult to write a useful title as I am not sure what function I need but hope someone can help out a bit

I am an English language teacher in Spain and I have acquired a list of key vocabulary items and definitions (with an example of the word being used in context e.g. column A "bossy" column B "Her bossy brother is always giving people orders".

I want to manipulate this data set a bit to make it a useful learning resource for my students by making the example appearing with asterisks e.g. "Her ***** brother is always giving people orders"s so I can import this to Quizlet.

I think it is important for the words (there can be two sometimes) to match the asterisks perfectly and this is where my problem lies. I have had a go (not a VBA professional but usual can get things to work after a few hours) and produced the code below but can anyone else shed some light.

Thanks



Dim Word As String
Dim jString As String
Dim Astrik As String

Dim strToSearchFor As String
Dim intPosition As Integer


Sheets("Entries").Select
FinalRow = Range("A9999").End(xlUp).Row


For i = 2 To FinalRow
Sheets("Entries").Select
jString = Len(Range("A" & i))
Word = Trim(Range("A" & i).Value)
strToSearchFor = " "
intPosition = InStr(1, Word, strToSearchFor)

If intPosition = 0 Then


If jString = "1" Then Astrik = "_"
If jString = "2" Then Astrik = "__"
If jString = "3" Then Astrik = "___"
If jString = "4" Then Astrik = "____"
If jString = "5" Then Astrik = "_____"
If jString = "6" Then Astrik = "______"
If jString = "7" Then Astrik = "_______"
If jString = "8" Then Astrik = "________"
If jString = "9" Then Astrik = "__________"
If jString = "10" Then Astrik = "__________"
If jString = "11" Then Astrik = "___________"
If jString = "12" Then Astrik = "____________"
If jString = "13" Then Astrik = "_____________"
If jString = "14" Then Astrik = "______________"
If jString = "15" Then Astrik = "_______________"



'find and replace in cell
Range("D" & i).Select

ActiveCell.Replace What:=Word, Replacement:=Astrik

Else

End If

Next i



End Sub

 

[MARK858]

Try the below. Word in Column A, string to search in Column D as per your code.

Sub zzzz()
    Dim Word As String
    Dim jString As Long, FinalRow As Long
    Dim Astrik As String, i As Long

    Dim strToSearchFor As String
    Dim intPosition As Long


    With Sheets("Entries")
        FinalRow = .Range("A9999").End(xlUp).Row

        For i = 2 To FinalRow
            jString = Len(.Range("A" & i))
            Word = Trim(.Range("A" & i).Value)
            strToSearchFor = .Range("D" & i)
            intPosition = InStr(1, strToSearchFor, Word)

            If intPosition <> 0 Then

                If jString = 1 Then Astrik = "_"
                If jString = 2 Then Astrik = "__"
                If jString = 3 Then Astrik = "___"
                If jString = 4 Then Astrik = "____"
                If jString = 5 Then Astrik = "_____"
                If jString = 6 Then Astrik = "______"
                If jString = 7 Then Astrik = "_______"
                If jString = 8 Then Astrik = "________"
                If jString = 9 Then Astrik = "__________"
                If jString = 10 Then Astrik = "__________"
                If jString = 11 Then Astrik = "___________"
                If jString = 12 Then Astrik = "____________"
                If jString = 13 Then Astrik = "_____________"
                If jString = 14 Then Astrik = "______________"
                If jString = 15 Then Astrik = "_______________"

                'find and replace in cell
                .Range("D" & i).Replace What:=Word, Replacement:=Astrik

            End If

        Next i
    End With


End Sub

 

[johnodocs]

Hi

thanks for the reply. I am not quite there yet.

I really want to have the example to the separate words clearly indicated with the ***

back to the drawing board

 

[MARK858]

Hi

thanks for the reply. I am not quite there yet.

I really want to have the example to the separate words clearly indicated with the ***

back to the drawing board

Can you elaborate? preferably with a before and after sample of what you want to achieve.

 

[johnodocs]

No problem - I will try again as I think it is an interesting problem that someone would enjoy solving.

I have 2 columns A and B.

"A" contains English words or phrases. A mixture of nouns, adjectives and verbs e.g. fussy, have the last laugh, home-made - there are over 1,000
"B" contains an example sentence with the target words or phrases being used e.g. my brother is so fussy. He always wants to clean up, mum makes the best home-made curry. It tastes the best.

So what I want to do is make these words or phrases appear as ****** where each * represents a letter of the word so fussy would be ***** (4 x *) and home-made would be ****-****. This is important so students can eliminate other words which don't fit the word number.

So to summarize - I want to turn this into a learning resource so I can ask the students questions as all the words on the list are required for this level.

Hope that helps

John

So want I want to do is

 

[MARK858]

Still a bit confused as what is the code posted not doing other than you need to change the D to A in the below.

.Range("D" & i)

 

[MARK858]

Typo in the last post corrected below.

Still a bit confused as what is the code posted is not doing other than you need to change the D to B in the below.

.Range("[B]D[/B]" & i).Replace What:=Word, Replacement:=Astrik

 

[johnodocs]

Hi Mark

I might have to fall on my sword.

The code i posted was my poor attempt to solve this problem

What am I looking for is a solution

 

[MARK858]

Unless we understand the problem we can't give a solution. Run the code I posted after changing the D to a B and then let me know what needs to be different.

At the moment the code I posted will change the same word in the cell in column B as the word in column A with the *** on the same row.

Like I said I need to know what you need different to the result you get with that code not the original code you posted.

 

[johnodocs]

Hi Mark

I ran the code and single words like bossy get replaced OK (I am using ^ now as the character to replace them).

The problem lies with the cells that contain two words e.g. "tree house" - it changes to ^^^^^^^^^^, not taking into account the separate word. "^^^^ ^^^^^" is how it would need to be.

Also adjectives like "happy-go-lucky" come out like " ^^^^^^^^^^^^^^" whereas I would need "^^^^^-^^-^^^^^"

Is this possible?


Thanks