VBA Referencing 2

**RBI** · 10-01-2008, 11:16 AM

I was wondering if using INDIRECT() function, or something like it in VBA, I could set a range as a reference instead of a fixed value... example below:

Instead of the range being fixed on the first line,

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**BigBas** · 10-01-2008, 11:21 AM

You can reference the value in the cell as text. In the example below, cell A1 has the text B1:C8 enterred. The macro below will highlight range B1:C8

Please Login or Register  to view this content.

Report back

**Cheeky Charlie** · 10-01-2008, 11:22 AM

Please Login or Register  to view this content.

HTH

**RBI** · 10-01-2008, 12:05 PM

Hey,

Thanks guys it worked... i'm attaching the file because something is happening to some of the formulas once I run this macro... its very strange. i changed the settings for the skipblank_ in the pastespecial function to true so i don't loose my other formulas. Please open the sheet and run macro4 and notice the formulas in "sheet1" column "N" before and after the macro is run... this column should not be affected but for some reason the values are showing up blank even with a formula in the cell that doesn't return a blank value?

Please help

**Cheeky Charlie** · 10-02-2008, 05:25 AM

Doesn't do anything to sheet1 column "N" on my computer...

Those formulae are ghastly, so I won't begin to try to decipher. I can, however, recommend the use of Tools-> Formula Auditing -> Evaluate formula

HTH