Weird .Find problem of split text/string.

[razzandy]

Hi Guys

This has been driving me nuts ?

I have a set of words separated by spaces in a cell and what I am trying to achieve is a find on the individual words rather than the full list of words in the cell. It does work but only on the whole cell contents rather than the individual words! C seems to read ST(mw) incorrectly!

Below is the part of the code that's not working correctly. It manages to split the multiple words/string from a cell and add an individual word/string to ST(mw) but when I check in the immediate window it assigns the full string containing all the words to C which is from the original string. What am I missing?

Thanks in advance

With ProductList.Range("b2:b" & RW) ' Find Similar keywords
Dim kWord As String: kWord = CrossSell.Range("b2")
Dim ST
Dim LA As XlLookAt

    ST = kWord
    ST = InputBox("Enter a Differant Search Term or accept default.", _
    "Search Term for Similar Items?", ST)
    If MsgBox("Click Yes to Search Each Word individually or No to search as one!", _
    vbYesNo + vbCritical, "Search Each Word or Both as One?") = vbYes Then
    ST = Replace(ST, " ", vbNewLine)
    LA = xlPart 'xlPart
    Else
    LA = xlWhole 'xlWhole
    End If
    ST = Split(ST, vbNewLine)
 
    For mw = 0 To UBound(ST) 'mw stands for multi word. Checks if multi word if it is it loops each keyword

    Set c = .Find(ST(mw), LookIn:=xlValues, LookAt:=LA)     '******* PROBLEM HERE ************
 
    If Not c Is Nothing Then
        firstaddress = c.Address
        Do

 

[Fluff]

I've no idea what you're trying to do, but "c" is a range & therefore it's value will be the entire contents of the cell.

 

[razzandy]

I've no idea what you're trying to do, but "c" is a range & therefore it's value will be the entire contents of the cell.

Rrrrrrrrr C is showing what its found and that's whats confused me! But thanks for your response because that got me thinking more which made me find this. The problem must be with my loop. I will investigate more then post back.

Thanks again

 

[Fluff]

You're welcome & thanks for the feedback