Solved

Type MisMatch

Posted on 2010-11-18
10
638 Views
Last Modified: 2012-06-21
All of a sudden I am getting a Type Mismatch  error on the following first section of code and can not figure out why? The code has been running fine for the last week????
Can you suggest something?

Sub SenClient()

    If Application.Evaluate("=MATCH(LEFT(B15,12),LEFT(FIIS,12),0)") Then 

MsgBox "Careful Sensitive Client", vbOKOnly, "Sensitive Client"

End Sub

Open in new window

0
Comment
Question by:llawrenceg
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 19

Expert Comment

by:Ken Butters
Comment Utility
you have to check the data contained in both B15, and in FIIS.  you might be comparing a text string against a number.... or against "N/A" or something.  If you are comparing against a number... you could add something like ("=MATCH(LEFT(cstr(B15),12),LEFT(cstr(FIIS),12),0)").... to make sure you only have strings.
0
 

Author Comment

by:llawrenceg
Comment Utility
Buttersk:
Thank you for the advice. I rechecked  B15 and reformatted it as Test and I checked FIIS ( A named Range for AI2-AI31) which was already text. However, I still get the Mismatch error.
What else can I do ?
0
 
LVL 7

Expert Comment

by:John15-16
Comment Utility
I would imagine from your few lines of code that due to the nature of this workbook it is not something you can post on here, am I correct?  That would be helpful to see, but I understand if it cannot be uploaded.
0
 
LVL 6

Expert Comment

by:sijpie
Comment Utility
What do you get when you put the formula in a cell? Does it compute then or do you get an error code (#NAME or something)? The rrror code will give some further idea of where the error lies.
Let us know.
0
 
LVL 19

Expert Comment

by:Ken Butters
Comment Utility
At a minimum, we need to see the data from b15 and the data from each of the cells in fiis
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:llawrenceg
Comment Utility
The Data is B15 is a string copied from the Windows Clipboard "Plan # 23456"
FIIS  is a named range for a list. The list consists of Plans . Plan # 23345, Plan # 4567, Plan # 5678, Plan # 7896, all strings
0
 
LVL 6

Expert Comment

by:sijpie
Comment Utility
Repeat from my earlier question: What do you get when you put the formula in a cell?
0
 
LVL 7

Assisted Solution

by:John15-16
John15-16 earned 200 total points
Comment Utility
If you use this in a cell it only works if entered as an array formula.  Can you try adding the ".FormulaArray" to your code?

0
 
LVL 33

Accepted Solution

by:
Norie earned 300 total points
Comment Utility
Why not try using Application.WorksheetFunction.Match instead of Evaluate?

Or perhaps even some native VBA functions?

You might also want to look at using COUNTIF which can use wildcard characters.

=COUNTIF(LIIS, LEFT(B15,12)&"*")

That formula would return 0 for no match and 1+ if there is a match(s).
0
 

Author Closing Comment

by:llawrenceg
Comment Utility
Thank you for your comments and advice
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now