WildCard Search Help Needed

[dhunton]

I'm going to try to make this explanation as simple as possible, but it's a fairly complex task, so let me know if I'm not clear.

I have a sheet with data imported from another program (in which work orders are created and completed with closing resolutions). I have another sheet: column 1 has a list of possible phrases used by the techs for their resolutions, column 2 has the "correct" resolution for my report.

The problem is that some of the abbreviations in column 1 need to have wildcards in them. An example would be "room * rehab" which would match "room in rehab" or "room being rehabbed" but not "rehabbed all items in room" which is also a possibility.

Is there some other way to do this that I'm missing?

 

[Norie]

What is you want to do?

 

[FDibbins]

You will need to show us some samples of what you have - and what you want, along with showing where this needs to go (to show context)

 

[dhunton]

OK, for some reason, I never seem to be able to make my data look right on this forum, but here's what I have (showing only the relevant columns, using ; as separator). I am running through an Until Found loop, which of course exits as soon as it finds any abbreviation that fits.

IMPORTED DATA SHEET
Fixed bed
Room in rehab
Room being rehabbed
Tech rehabbed all items in room
Checked all

ABBREVIATION LIST
Bed; Bed repaired by technician
Checked all; Technician checked all, no trouble found
Room * rehab; Room undergoing rehab, will recheck when complete
* all *; Technician repaired all damaged items in room

RESULT NEEDED:
Bed repaired by technician
Room undergoing rehab, will recheck when complete
Room undergoing rehab, will recheck when complete
Technician repaired all damaged items in room
Technician checked all, no trouble found


I hope that helps. I have an additional "resolution" that is a default, and it is not "Technician repaired all damaged items in room" so I can't just set that to be what's used if nothing is found. In addition, I have other choices that use the word "checked" so I can't just use that to search for checked all.

 

[dhunton]

If you'd like, I can paste all the relevant portions of the Sub in here, but I don't know how to keep the formatting, and without indents, it would get pretty messy. LOL

 

[dhunton]

OK, I'm on Eastern time, so it's time for me to log off for the night, but I figure some of you might still be able to have a look while I'm gone, so I'll post the code here.

Sub ExtractResolution()
 
     Dim CheckSheet As Worksheet
     Dim TargetSheet As Worksheet
     Dim SourceSheet as Worksheet
     Dim LastSourceRow as Integer
     Dim LastAbbrRow As Integer
     Dim LoopCounter As Integer
     Dim CurrentRow As Integer
     Dim Found As Boolean               
     Dim RowFoundIn As Integer        
   
     ErrorFound = False
     Found = False
     RowFoundIn = 0
   
     Set CheckSheet = ‘ABBREVIATION LIST in my previous post
     Set TargetSheet = ‘RESULT NEEDED in my previous post
     Set SourceSheet = ‘IMPORTED DATA SHEET in my previous post
     LastSourceRow = SourceSheet.Range(“A1”).End(xlDown).Row
     LastAbbrRow = CheckSheet.Range("A1").End(xlDown).Row
   
     'LoopCounter tracks row in SourceSheet which holds the CloseNote being checked
     'CurrentRow tracks row in CheckSheet that is being looked for in the CloseNotes
     For LoopCounter = 2 To LastSourceRow
          CurrentRow = 1
          Do Until Found
               CurrentRow = CurrentRow + 1
           
               If CurrentRow > LastAbbrRow Then
                    ErrorFound = True
                    Found = True
               Else
                    'Look for abbreviation in Col 1 of CheckSheet in Col 3 of SourceSheet
                    If InStr(1, SourceSheet.Cells(LoopCounter, 3), CheckSheet.Cells(CurrentRow, 1), vbTextCompare) >= 1 Then
                         Found = True
                         RowFoundIn = CurrentRow
                    End If
               End If
                       
          Loop 'Until Found Loop searching on CheckSheet
       
          If ErrorFound Then
               ‘Print Error Message of "No resolution found" style
          Else
               TargetSheet.Cells(LoopCounter, 29).Value = CheckSheet.Cells(RowFoundIn, 2).Value
          End If
  
          ErrorFound = False
          Found = False
  
     Next LoopCounter
 
     ErrorFound = False
 
End Sub

 

[dhunton]

OK, what I think I have it narrowed down to is why this doesn't work...

Dim String1 As String
Dim String2 As String
Dim TestString As String
Dim Location As Integer

String1 = "Hello"
String2 = "Goodbye"
TestString = "Hello and Goodbye"
Location = InStr(1, TestString, (String1 & "*" & String2), vbTextCompare)

I've tried it as:
String1 & * & String2
String1 & "*" & String2

Neither way works. The first doesn't compile, and the last doesn't use the * as a wildcard.