.Find Next

[pike]

Hi All
Have problems using find and the Dictionary
What Im trying to do is find a certain word in a string then return the number associated with that word
The code is

Dim DXFDict,fAddress As String,LookInR As Range,
Dim DXFDescription, DXFCode, Key,FoundOne As Range
  Set DXFDict = CreateObject("Scripting.Dictionary")
   DXFDict.Add "ROAD", "10"
   DXFDict.Add "TREE", "24"
   DXFDict.Add "FENCE", "30"
   DXFDict.Add "GATE", "31"
   'DXFDict.Add "", "0"
      Set LookInR = Sheets("Orienteering").Range("E:E").CurrentRegion
          For Each Key In DXFDict
              With LookInR  'Columns("E")
                 Set FoundOne = .Find(What:=Key, LookAt:=xlPart) 
                 fAddress = FoundOne.Address "ERROR ON THIS LINE
                   Do
                     FoundOne.Offset(1).Value = DXFDict.Item(Key)
                    Set FoundOne = .FindNext(After:=FoundOne)
                  Loop While FoundOne.Address <> fAddress
               End With
          Next

But I get an error on this line fAddress = FoundOne.Address
why is this so??
any pointers appreciated

[Andy Pope]

If the text is not found then the object FoundOne will be nothing so you need to test for that first.

    Dim DXFDict, fAddress As String, LookInR As Range
    Dim DXFDescription, DXFCode, Key, FoundOne As Range
  Set DXFDict = CreateObject("Scripting.Dictionary")
   DXFDict.Add "ROAD", "10"
   DXFDict.Add "TREE", "24"
   DXFDict.Add "FENCE", "30"
   DXFDict.Add "GATE", "31"
   'DXFDict.Add "", "0"
      Set LookInR = Sheets("Orienteering").Range("E:E").CurrentRegion
          For Each Key In DXFDict
              With LookInR  'Columns("E")
                 Set FoundOne = .Find(What:=Key, LookAt:=xlPart)
                 If Not FoundOne Is Nothing Then
                    fAddress = FoundOne.Address 'ERROR ON THIS LINE
                      Do
                        FoundOne.Offset(1).Value = DXFDict.Item(Key)
                       Set FoundOne = .FindNext(After:=FoundOne)
                     Loop While FoundOne.Address <> fAddress
                 End If
               End With
          Next

[pike]

That was quick Andy

Check if object Exist before proceeding .. thanks for that
also does Find look for the Word with in sting or do I need to do something like

 Set FoundOne = .Find(What:="*" & Key & "*", LookAt:=xlPart)

as it looking like the word "GATE" is not found in the String "Big Gates"

[Andy Pope]

xlPart implies *gate*

[pike]

xlPart implies *gate*

thought so Himmm ....
may have the next Dict item in the wrong place ... I'll have a play around
your direction has help alot. I can stop scratching my head and move on
Thanks for your help again ...

[Phil_V]

The 'lookat:=xlPart' is telling Excel that you are wanting partial matches as well.

"gate", "big gate", "really big gated drive", "segregated" will all match a search pattern of "gate", (and be careful of cases like the 3rd & 4th one's as they can really catch people out if you are searching for short strings in particular)

If you only wanted exact matches you would use 'lookat:=xlWhole', which would then only match the first case in the example above.

I don't know, but it's quite possible that 'Find' is case sensitive? Try searching for "Gate" and also "GATE" and see if you get different results?

[pike]

thanks phil_V

[pike]

Spelling mistake Big Gote
xl part working like a dream