Re: [ExcelVBA] Reg Ex in VBA?

 

Please copy the actual formula out of the cell into an e-mail. I think you
will find it is wrong.

> "=IF(AND(EXACT(J2,RegExpSubstitute(J2,""[^0-9]"","")), LEN(J2) =
> 7),TRUE,FALSE)"

I believe this will become

=IF(AND(EXACT(J2,RegExpSubstitute(J2,"[^0-9]",")), LEN(J2) = 7),TRUE,FALSE)

where

(J2,"[^0-9]",")

has an unbalanced quote after the second comma.

You possibly want

> "=IF(AND(EXACT(J2,RegExpSubstitute(J2,""[^0-9]"","""")), LEN(J2) =
> 7),TRUE,FALSE)"

i.e. four quote marks which would become "" in the formula.

Regards, Dave S

----- Original Message -----
From: "Saurabh Kushwaha" <saurabh.rdb@gmail.com>
To: <ExcelVBA@yahoogroups.com>
Sent: Wednesday, September 26, 2012 9:37 PM
Subject: Re: [ExcelVBA] Reg Ex in VBA?

> Hi,
>
> I am using Reg Ex in my recent project. I have to make a validation to
> check whether the figure in a particular column is a 7-digit integer. But
> somehow, it is showing me an error. I am not able to Call the function of
> reg ex from my subroutine. Here is the code:
>
> Sub RegExp()
>
> Range("J2:J15").Select
> With Selection.Validation
> .Delete
> .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop,
> Operator:= _
> xlBetween, Formula1:="=IF(AND(EXACT(J2,
> RegExpSubstitute(J2,""[^0-9]"","")), LEN(J2) = 7),TRUE,FALSE)"
> .IgnoreBlank = False
> .InCellDropdown = True
> .InputTitle = ""
> .ErrorTitle = ""
> .InputMessage = ""
> .ErrorMessage = ""
> .ShowInput = True
> .ShowError = True
> End With
>
> End Sub
>
> Function RegExpSubstitute(ReplaceIn, ReplaceWhat As String, ReplaceWith As
> String)
>
> #If Not LateBind Then
> Dim RE As RegExp
> Set RE = New RegExp
> #Else
> Dim RE As Object
> Set RE = CreateObject("vbscript.regexp")
> #End If
> RE.Pattern = ReplaceWhat
> RE.Global = True
> RegExpSubstitute = RE.Replace(ReplaceIn, ReplaceWith)
>
> End Function
>
> Please help!!!
>
>
> On Tue, Sep 25, 2012 at 4:44 PM, Saurabh Kushwaha
> <saurabh.rdb@gmail.com>wrote:
>
>> Thanks Ashok!
>>
>>
>> On Tue, Sep 25, 2012 at 3:19 PM, ashok kumar.s.v
>> <ashokkumarsv@yahoo.com>wrote:
>>
>>> **
>>>
>>>
>>> hope this link will be useful
>>>
>>> http://lispy.wordpress.com/2008/10/17/using-regex-functions-in-excel/
>>>
>>> Regards,
>>>
>>> Ashok kumar.S.V
>>>
>>>
>>>
>>> ________________________________
>>> From: Saurabh Kushwaha <saurabh.rdb@gmail.com>
>>> To: ExcelVBA@yahoogroups.com
>>> Sent: Tuesday, September 25, 2012 2:00 PM
>>> Subject: [ExcelVBA] Reg Ex in VBA?
>>>
>>>
>>>
>>> Hi,
>>>
>>> Does anyone know how to use Regular Expressions in Visual Basic? Any
>>> useful
>>> link or tutorial would be of great help.
>>>
>>> Also, how to use 'Like' operator in VBA?
>>>
>>> Thanks and Regards
>>> --
>>> Saurabh Kushwaha
>>> Class of 2011, NSIT, Delhi University, India
>>>
>>> [Non-text portions of this message have been removed]
>>>
>>> [Non-text portions of this message have been removed]
>>>
>>>
>>>
>>
>>
>>
>> --
>> Saurabh Kushwaha
>> Class of 2011, NSIT, Delhi University, India
>>
>>
>>
>
>
> --
> Saurabh Kushwaha
> Class of 2011, NSIT, Delhi University, India
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> ----------------------------------
> Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
> here: http://www.mousetrax.com/techtrax to enter the ezine, then search
> the ARCHIVES for EXCEL VBA.
>
> ----------------------------------
> Visit our ExcelVBA group home page for more info and support files:
> http://groups.yahoo.com/group/ExcelVBA
>
> ----------------------------------
> More free tutorials and resources available at:
> http://www.mousetrax.com
>
> ----------------------------------Yahoo! Groups Links
>
>
>
>
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 10.0.1427 / Virus Database: 2441/5292 - Release Date: 09/25/12
>

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (6)

Recent Activity:

• New Members 3

Visit Your Group

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___