Trouble defining Sub routine in XL10

[Taylor714]

Hello,

I'd appreciate any help on this as I'm going through the forums and don't appear to be finding an answer that works but I'll give you all the information I can and hopefully there may be a simple answer.

I'm getting the "Compile error: Sub of Function not defined" message and then points to the Search function as below.

FYI I'm using Office/Excel 2010 in Windows XP.

I've managed to get the Evaluate function to work (put in comments below) but I haven't been successful taking this forward as I need the macro to work with relative referencing (if I'm using the right term correctly) as I would like it to read other cells. By trying to put in a variable instead of the cell references didn't seem to work in the evaluate function. Therefore tried to develop another piece of code as below.

I've thought it might be a case that I haven't got the right References selected i.e. Tools>References and have the following 'ticked' but again still doesn't work:-

Visual Basic for Applications
Microsoft Excel 14.0 Object Library
OLE Automation
Microsoft Office 14.0 Object Library
Microsoft Office 14.0 Access database engine Object Library
Solver

The code I'm using at the moment is as below:-

Public Sub SecondAttempt()


Worksheets("Bill").Activate
Range("B5:F5").Select


If Count(Search(""" * masonry * """ & """ * bill * """, Range("b5:f5"))) = 2 Then
MsgBox "Match Found"
Else
MsgBox "No Match Found"
End If


'BELOW FORMULA WORKS BUT WITHOUT DYNAMIC RANGE
'If Evaluate("Sum(CountIf(B5:F5,{""*masonry*"",""*bill*""}))=2") Then
' MsgBox "Match Found"
'Else
' MsgBox "No Match Found"
'End If


End Sub

Again, appreciate any help on this as struggling to find what I would've thought was an easy fix..!

 

[Taylor714]

Just to update - may or may not be the right direction but I changed the code to below basically adding WorksheetFunction.Count and WorksheetFunction.Search

and now I get run-time error '13': type mismatch

still not sure what the solution is but thought I'd post it just in case its useful.



If WorksheetFunction.Count(WorksheetFunction.Search("""*masonry*""", Range("b5:f5").Cells)) > 0 Then
MsgBox "Match Found"
Else
MsgBox "No Match Found"
End If

 

[Taylor714]

Evening,

To update progress.......... the following almost seems to work..... I've tried replacing the + signs with &'s but that doesn't work either. The formula works if I use > 0 but I need the formula to recognise a string of text rather than just one word:-

If WorksheetFunction.Sum(WorksheetFunction.Count(Range("B5:F5"), ("*masonry*" + "*support*" + "*system*"))) = 3 Then
MsgBox "Match Found you're epic"
Else
MsgBox "No Match Found"
End If

I've also tried CountIf but again that doesn't work.

so with a little finesse I'm hoping someone can fill in a little expertise here............?

 

[Taylor714]

I get the impression I may be talking to myself here but if any one may have been looking at this - I think I've found the solution. so problem solved thank you very much.