VBA Error on Excel: Method or Data Member not found -
my code:
public sub splitupregexpattern() dim regex new regexp dim strpattern string dim strinput string dim strreplace string dim myrange range set myrange = activesheet.range("b2:b4279") each c in myrange strpattern = "([a-z]{2}\/[a-z]{2}\/[a-z][0-9]{2}\/[a-z]{3}[0-9]{9}\/)([0-9]{4})" if strpattern <> "" strinput = c.value strreplace = "$1" regex .global = true .multiline = true .ignorecase = false .pattern = strpattern end if regex.test(strinput) c.offset(0, 1) = regex.replace(strinput, "$2") else c.offset(0, 1) = "" end if end if next
end sub
it working well, give me error, still complete task doing. when use macro on new spreadsheet, gives me error:
compile error: method or data member not found.
all solutions on site tailored different situations, couldn't apply them circumstance unfortunately.
i have no idea why happening, haven't changed of code. sure if had greater understanding vba script able understand this, not, came find out if here me!
thanks!
aydan
you need add reference library called "microsoft vbscript regular expressions 5.5" make work.
if code works in workbook means have added library reference , when copy code new workbook there need add same reference again.
in existing code auto instantiating variable called redex assumes library reference has been added make work properly.
to avoid this, may use late binding technique not require add reference , code work on workbook.
to declare regex variable object below...
dim regex object set regex = createobject("vbscript.regexp")
Comments
Post a Comment