Microsoft Office Forums - View Single Post

BobBridges · 04-07-2020, 01:20 PM

Interesting! Off-hand I don't think you can do it outside VBA. But in VBA it should be pretty simple. You didn't say where you want that formula to go, so you'll have to paste in your own range. And I'm used to R1C1 notation (for obvious reasons), so I may not get the A1-style notation just right, but I'm sure you can fix it if I make a mistake:

Code:

Select Case Len(Range("A1").Value) 'or wherever the filename is
  Case 21: vf = Range("$O$2").Value
  Case 22: vf = Range("$O$3").Value
  Case Else: 'you didn't say what in this case
  End Select
Range(target range).Formula = vf

If you write the formulae in R1C1, the property is FormulaR1C1.

That's assuming that in $O$2 and $O$3 you have the formulae in quotes. If you have the actual formulae, I guess it would look like this:

Code:

Select Case Len(Range("A1").Value) 'or wherever the filename is
  Case 21: vf = Range("$O$2").Formula
  Case 22: vf = Range("$O$3").Formula
  Case Else: 'you didn't say what in this case
  End Select
Range(target range).Formula = vf

But in that case you might get into issues with addressing; you'd have to remember to make the formulae in column O point to the correct rows and columns after coping them to your target range.

I haven't tested this code, but it's how I would attempt it.