You can use this for text also
Sub isExist()
On Error Resume Next
If WorksheetFunction.Match(Range("rngValues").Text,
Worksheets("Sheet2").Range("d4:d10"), 0) Then
If Err.Number = 1004 Then
Err.Clear
On Error GoTo 0: On Error GoTo -1
MsgBox "Values not found"
Else
MsgBox Range("rngValues").Value
End If
End If
End Sub
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Pascal Baro
Sent: Jan/Sun/2012 09:44
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Need help in Excel-VBA code to write function in
coding
Hi Ashish,
Please find another version. With this, I've worked out the fact your data
source is only numbers but either it is entered as text either it is
copy/paste and excel reads it as text. Some of the things I have tried woud
take an input such as 76 and look for everything in the data source that
matches 76 ex.: 7690 would match... So here I consider you only want to
compare number. Tough my code is quite basic if someone looks at it.
Applying Rajan_Verma
Sub isExist()
On Error Resume Next
If WorksheetFunction.Match(Worksheets("Sheet1").Range("b4").Value,
Worksheets("Sheet2").Range("d4:d10"), 0) Then
If Err.Number = 1004 Then
Err.Clear
On Error GoTo 0: On Error GoTo -1
MsgBox "Values not found"
Else
MsgBox Range("rngValues").Value
End If
End If
End Sub
The code above returns false if you compare 8902 to "8902", the first one is
a number, the second one is a text... and I can't make it work if I format
8902 as a number, nothing happens. Why?
While I'm learning, I'm helping so sorry for asking a question that might
not be of your concern Ashish...
HTH,
pascal baro
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
----------------------------------------------------------------------------
--------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
