PDA

View Full Version : Searching for multiple values in string



LaBamba
06-24-2014, 12:33 PM
Hi,

I would like to validate my data in the simplest way possible.

I need to confirm that all column headers that i point out are available.
I can create a cell with all the headers I need. I need the macro to confirm all headers are in the workbook I am working.

Let me know your thoughts.

Thanks,

EirikDaude
06-24-2014, 01:06 PM
Is this what you'd like to do, or are you looking for something more like a worksheet-function?

Option Explicit

Sub check_headers()
Dim headers As Variant, v As Variant, r As Range, allFound As Boolean
' Put the list of possible headers into an array
headers = Array("Header1", "Header2", "Header3")

' Set r to the area of the sheet which contain the headers
Set r = Sheet1.Range("A1:A21")
allFound = True
For Each v In headers
If r.Find(CStr(v), LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) Is Nothing Then
allFound = False
Exit For
End If
Next

If allFound Then
MsgBox "All the headers were found"
Else
MsgBox "All the headers were not found"
End If
End Sub

LaBamba
06-24-2014, 01:14 PM
I think this will work perfectly for the time being; I would like the msgbox to include what is missing.

I did however intend for it to search a list of columns headers in my main workbook by using a parsed string...since i plan to use this for multiple files.

I will try your suggestion and report back =)

you roxx =)

EirikDaude
06-24-2014, 02:42 PM
I don't think it should be too hard to use e.g. a comma separated list (entered as a string to a cell) to populate the array instead of doing it explicitly. As for what area you want to search, you can define that where you set the range, but I guess you may have figured that out already :)

LaBamba
06-24-2014, 02:53 PM
I haven't figured out how to get the msgbox to return all headers not found...doesnt look like the script stops at the first false...so it should have them all!

I'm getting there...thanks for the assistance so far =)

EirikDaude
06-24-2014, 04:21 PM
The "exit for" cause the script to go out of that loop on the first header it can't find.

If you don't want it to do that, but instead create a message with all the missing header, replace the two lines inside the if statement with something like:

If allFound Then
allFound = False
s = CStr(v)
Else
s = s + " - " + CStr(v)
End If

And then append s to the message you put out with the MsgBox. Note that you also need to dim s as a string at the top of the sub.

LaBamba
06-25-2014, 09:39 AM
i think i destroyed the code,


Sub check_headers()
Dim headers As Variant
Dim v As Variant
Dim r As Range
Dim s As String
Dim allfound As Boolean
Dim txt As String


headers = Array("Item No", "NDC/UPC", "Manufacture", "Mfg", _
"Mfg Part No", "Description", "Plno", "Pedigree Req", "Dating")




Set r = ActiveSheet.Range("1:1")


allfound = True
For Each v In headers
If r.Find(CStr(v), LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) Is Nothing Then
allfound = False
End If

If allfound Then
allfound = False
s = CStr(v)
Else
s = s + "-" + CStr(v)
End If
Next

If allfound Then
MsgBox "All Headers were found"
Else
MsgBox s

End If


End Sub

LaBamba
06-26-2014, 02:18 PM
I will try to fix it again tomorrow; i feel like i butchered your code, hah.

Thanks Eirik

EirikDaude
06-26-2014, 05:23 PM
Inside the if-clause.

Does this work?

Sub check_headers()
Dim headers As Variant
Dim v As Variant
Dim r As Range
Dim s As String
Dim allfound As Boolean
Dim txt As String
headers = Array("Item No", "NDC/UPC", "Manufacture", "Mfg", _
"Mfg Part No", "Description", "Plno", "Pedigree Req", "Dating")
Set r = ActiveSheet.Range("1:1")
allfound = True
For Each v In headers
If r.Find(CStr(v), LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) Is Nothing Then
If allfound Then
allfound = False
s = CStr(v)
Else
s = s + "-" + CStr(v)
End If
End If
Next

If allfound Then
MsgBox "All Headers were found"
Else
MsgBox "Missing headers: " + s
End If
End Sub

LaBamba
07-24-2014, 11:09 AM
worked great! thanks!