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]

For the tree house scenario you can try the code below but for the happy-go-lucky scenario if you mean it is happy-go-lucky in Column A as well as Column B then I can't think of a way to cover it without doing another run of the code but with "-" as the split delimiter so unless someone else has an idea I think you are stuck with adding the duplicate code if that is what you want to do.

If you mean in Column A it is "happy go lucky" then the code below will handle that.

Sub zzzz()
    Dim jString As Long, FinalRow As Long, Astrik As String, i As Long
    Dim LString As String, LArray() As String, x As Long
    Dim strToSearchFor As String, intPosition As Long


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

        For i = 2 To FinalRow
            LArray = Split(Trim(.Range("A" & i).Value), " ")
            strToSearchFor = .Range("B" & i)
            For x = LBound(LArray) To UBound(LArray)

                intPosition = InStr(1, strToSearchFor, LArray(x))
                jString = Len(LArray(x))

                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("B" & i).Replace What:=LArray(x), Replacement:=Astrik

                End If
            Next x
        Next i
    End With
End Sub