VBA Problem.... One for someone far smarter than i am.!!!

[bundybear]

Hi all,

I am trying to use this user defined function called RANGEMATCH that i had a look at being used elsewhere being the following code

Function RANGEMATCH(SearchIn As Range, SearchFor As Range)
'
'
For Each Cell In SearchFor
If InStr(SearchIn, Cell) Then
RANGEMATCH = Cell.Value
Else
RANGEMATCH = ""
End If
Next Cell

End Function

[FONT=Helvetica Neue, Helvetica, Arial, san-serif]Then I am calling up the function and directing it to search my description cell (SearchIn) and then pointing it to my named data range (SearchFor) but it doesn't quite work. It does work if the last item say the 5th item out of a list of 5 in my search list is found but if it is the 1st, 2nd, 3rd or 4th in the list of 5 then this [/FONT][FONT=Helvetica Neue, Helvetica, Arial, san-serif]doesn't[/FONT][FONT=Helvetica Neue, Helvetica, Arial, san-serif] seem to work at all?????? [/FONT]

[FONT=Helvetica Neue, Helvetica, Arial, san-serif]I know i am probably missing something very simple but this is driving me nuts!!! [/FONT]

[FONT=Helvetica Neue, Helvetica, Arial, san-serif]Any ideas please???[/FONT]

 

[Michael M]

Hi Bundybear
Can you post a SMALL sample of what you have and what you expect as a desired outcome.
What InStr string are you searching for ?

 

[StephenCrump]

It looks like you have the InStr() arguments around the wrong way. This will return the cell value where the first match is found:

Dim cell As Range

For Each cell In SearchIn
    If InStr(cell, SearchFor) Then
        RANGEMATCH = cell.Value
        Exit For
    End If
Next cell

But would it perhaps be more useful to return the row number? Or the range reference?

This would be a simple tweak of your function, but let us know exactly what you're looking to achieve.

 

[bundybear]

Here is a sample of what i am trying to do. I want to be able to search the Description column in the below table for specific items of text.

Date Description Fault Reason
1/1 A/C won't work power circuit failure
5/1 ac not cold condenser iced up
7/1 air conditioning too hot User Set temp
9/1 Phone broken network Congestion
15/1 handset broken user user
24/1 temp to cold user set temp


My key search words would be like the following:

A/C ac air conditioning temp
Phone handset PABX telephone


What I want to do is search column 2 (Description) for any of the words in my other table, and then return in Column 5 the text that it found.

In the above table this is what i would want it to return in RED

Date Description Fault Reason FOUND TEXT
1/1 A/C won't work power circuit failure A/C
5/1 ac not cold condenser iced up ac
7/1 air conditioning too hot User Set temp air conditioning
9/1 Phone broken network Congestion phone
15/1 handset broken user user handset
24/1 temp to cold user set temp temp


After this then i can easily categorise each of the found key words so rows 1,2,3 & 6 would become a "Phone Issue" and rows 4 & 5 would become an "Air/Con Issue".

Hope that makes sense and thanks again for your suggestions so far.

 

[bundybear]

Sorry, I just realised all my spaces were removed so here is a picture of what i am trying to do. I have also included how i use the RANGEMATCH function in RED at the bottom of the Found Text column. I want to be able to just paste this formula down across many rows.

Thanks again.


[TABLE="width: 500"]
<tbody>[TR]
[TD]DATE[/TD]
[TD]Description[/TD]
[TD]Fault[/TD]
[TD]Reason[/TD]
[TD]Found Text[/TD]
[/TR]
[TR]
[TD]1/1[/TD]
[TD]A/C won't work[/TD]
[TD]power circuit[/TD]
[TD]failure[/TD]
[TD]A/C[/TD]
[/TR]
[TR]
[TD]5/1[/TD]
[TD]ac not cold[/TD]
[TD]condensor[/TD]
[TD]iced up[/TD]
[TD]ac[/TD]
[/TR]
[TR]
[TD]7/1[/TD]
[TD]air conditioning too hot[/TD]
[TD]user[/TD]
[TD]set temp[/TD]
[TD]air conditioning[/TD]
[/TR]
[TR]
[TD]9/1[/TD]
[TD]Phone broken[/TD]
[TD]network[/TD]
[TD]congestion[/TD]
[TD]Phone[/TD]
[/TR]
[TR]
[TD]15/1[/TD]
[TD]handset broken[/TD]
[TD]user[/TD]
[TD]user[/TD]
[TD]handset[/TD]
[/TR]
[TR]
[TD]24/1[/TD]
[TD]temp to cold[/TD]
[TD]user[/TD]
[TD]set temp[/TD]
[TD]temp[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=RANGEMATCH(B8:$A$11:$E$12)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SearchItems[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A/C[/TD]
[TD]ac[/TD]
[TD]temp[/TD]
[TD]air con[/TD]
[TD]air conditioning[/TD]
[/TR]
[TR]
[TD]Phone[/TD]
[TD]handset[/TD]
[TD]PABX[/TD]
[TD]telephone[/TD]
[TD]headset[/TD]
[/TR]
</tbody>[/TABLE]

 

[StephenCrump]

OK, it can be done using VBA, but will it really make your life easier?

Presumably there are reasonable numbers of these faults to be processed, for you to want an automated solution.

Does it make sense to return only the first find? For example, if the problem is "PABX too hot" or "lacks volume" then presumably these are not airconditioning problems?

If you want to allow multiple finds, how do you want these returned for easy sorting/classification?

Would it make sense to use Excel instead ... using a separate column to SEARCH() for each search term, and then sorting the results?

 

[bundybear]

Stephen you are right that it would be easier with excel only and maybe using filters etc except that i am searching through some 26,000 rows of data (and growing!!) and want to be able to search for say 100 different key words that could change over time hence my struggle with VBA!!

I think that if i can do the search, then return the search item into a cell on its own then i can use excel for further sorting etc but it is just that first step i am looking at in VBA now.

Thanks

 

[Michael M]

Have you considered using a Custom filter ?

 

[bundybear]

Michael, I have tried using an advanced filter and used my list of search items as the 'Criteria Range' but it doesn't seem to work either. I think it is trying to look for every item in my search range within the target cell rather than 1st OR 2nd OR.... etc.

Is there another way of filtering using a 'Contains' criteria that could point to a range of data using an OR rather than the AND?

 

[bundybear]

Michael, I now have advanced filters working and have created a table of say 20 categories each with 10 to 20 search items so i am running the custom filter 20 times (one for each category) and then copying and pasting into visible cells only. This works but gee an automatic version of this would save me heaps of time!

Thanks again for the suggestion.