PASTE FORMULA from lookup table

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

ChrisOK · #2 04-07-2020, 01:46 PM

I'm not sure if it needs to change to the R1C1 format or what?
I tried "A1" and no luck,
Also tried A1:A17 range - still getting error

Can you take a look at NEW attachment (saved as xlsm)
Not sure how to put it in the R1C1 format

Also, I'm trying to get the result to appear in column K currently for the test
But will then move it into col B (so the formula in B will adjust based on the length of the filename in col A

ChrisOK · #3 04-07-2020, 01:52 PM

I updated the test file again just now to
"A2" only (for the Len Range)

and then changed O2 to NOT have quotes on either side of the formula and it pasted the result of the formula (not the formula)

If I put the quotes back, it pastes the formula with quotes and therefore does not function as a formula

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

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