MrExcel #1 on Top 10 List...

[RoryA]

That's not me (as you well know). You have been quiet recently - busy?

 

[zorvek]

Very busy. And I got tired of Saurabh's ellipses.

 

[RoryA]

you...seem...to...have...got...him...to...stop...recently...though...

 

[Richard Schollar]

Rory, your ellipsist attitude worries me...

EDIT: Hi Kevin!

 

[zorvek]

Richard!

I have to say your VBE Immediate window trick for mashing cell constants is super cool and I have already scored points using it!

Thank you!

Kevin

 

[RoryA]

What trick is that?

 

[NateO]

Patrick gets around? Where is he?

The real question is whether Richard has used my Caller trick - someone got caught saying "it can't be done". (it's not very practical)

On a slightly more serious note, let's try to keep this friendly. And welcome, Kevin!

 

[zorvek]

What trick is that?

Constants in a range of cells can be altered in place using an Excel worksheet formula and without entering that formula in any cells. To do so, first select the cells to be altered. Press ALT+F11 to open the VBE and then press CTRL+G to open the Immediate window. Enter the formula to use to alter the cell constants using the following VBA statement:

Selection.Value = Evaluate("IF(ROW(),formula)")

To reference a specific cell or column use the Selection.Address method:

Selection.Value = Evaluate("IF(ROW(),RIGHT(""00000""" & Selection.Address & ",5))")

The above example pads all constant values with enough zeroes to make the constant five characters long.

 

[zorvek]

...used my Caller trick - someone got caught saying "it can't be done". (it's not very practical)

Link?

 

[RoryA]

Oh, that one...