Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
replacerx to turn Excel column reference into finite range
#1
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
#2
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)
#3
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
#4
bump for the stumped Sad
#5
Too difficult to me.
Google for a regular expression tool. It can help you learn and build regular expressions.
#6
what language/syntax does QM borrow for RegExp?
I can't just google Regular Expressions without knowing that can I?
#7
QM uses PCRE library. It is >90% compatible with Perl regular expressions. This syntax is mostly used.


Forum Jump:


Users browsing this thread: 1 Guest(s)