PASTE FORMULA from lookup table

BobBridges · #2 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.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
LOOKUP Formula error but WHY???	Haga	Excel	1	04-17-2019 11:13 PM
N/A Value in Lookup Formula	NickFazer	Excel	2	04-02-2019 04:22 AM
How to update lastname with the help of V-Lookup formula?	Mangesh1212	Excel	6	12-05-2018 02:15 AM
Need help with lookup formula	tristanlau	Excel	1	08-14-2017 07:16 AM
Lookup / Paste Multi Values VBA	Mav	Excel Programming	6	05-10-2015 08:42 PM