replacerx to turn Excel column reference into finite range

[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