replacerx to turn Excel column reference into finite range

[frank]

Dealing with Excel 03 and 07 a lot of time I have to convert a columns reference to a finite range.
I cannot figure out how to use replacerx to do this (I am new to replacerx)

Replace: $A:$BZ
With: $A$1:$BZ$55555
Where: A and BZ are any letters and may be 1 or 2 in a row. (as above)

I cannot figure out how to "remember" and return ":$BZ" to add the 55555 after it with replacerx

The Help for replacerx mentions nothing about it.

Regexp help mentions it but I cannot figure out the syntax.

If you can help please explain the basics of the remember feature (and how to code the example) I'd really appreciate it
thanks

[Gintaras]

Macro

Code:

Copy      Help

str s="$A:$BZ"
s.replacerx("^(\$A):(\$BZ)$" "$1$$1:$2$$55555")
out s

In replacerx, the first string is regular expression (rx), the second - replacement.

In rx:
^ - beginning
() - remembers the enclosed part
\$ - $ (because $ is special symbol, it is with \)
$ - end

In replacement:
$1, $2 - first and second remembered parts
$$ - $ (in replacement, $ is special symbol, and $$ is its escape sequence)

[frank]

Hi Gintaras,

It seems to work if “$A:$BZ”
-is the only part of the formula
-and the range is limited to $A:$BZ

The formula I’m trying to get it to work in looks like this:

=IF(OR(K89="Yes",K89=" Delete"),99999,VLOOKUP($A89,'H:\!Files\Live Data\[GBDataSource.xlsm]Inventory'!$A:$V,22,FALSE))

I modified your statement to match $A:$V but it didn’t work.

With ONLY $A:$V it worked but not with the whole formula.

Though I am stuck there, later I need to allow for any letters

I tried
Macro

Code:

Copy      Help

s.replacerx("^(\$[A-Z]{1,}):(\$[A-Z]{1,})$" "$1$$1:$2$$55555")

But not sure if it’s right b/c it doesn’t work hardcoded anyway.

Thanks

[frank]

bump for the stumped

[Gintaras]

Too difficult to me.
Google for a regular expression tool. It can help you learn and build regular expressions.

[frank]

what language/syntax does QM borrow for RegExp?
I can't just google Regular Expressions without knowing that can I?

[Gintaras]

QM uses PCRE library. It is >90% compatible with Perl regular expressions. This syntax is mostly used.