Writing a "dynamic" formula in a cell

Question

The cell is not filled with the formula, but a "FALSE" is written in there. Any ideas how to change that, so that the formula is displayed correctly? No error message though..

'code
q = 1
Do Until q = 10
   q = q + 1
   If Cells(q, 4).HasFormula Then
   Else
     If IsEmpty(Cells(q, 4)) Then
     Cells(q, 4).Formula = Cells(q, 4).Formula = "=IFERROR(VLOOKUP(E" & q & ",Lagerplätze!A:B,2,FALSE),)"
     End If
   End If
Loop

'code

EDIT: updated code

Thanks a lot!

What do you mean with "it isn't working properly"? Does it throw an error? It doesn't do anything? It does something, but not what you expected? — AJPerez
– AJPerez, Commented Sep 10, 2014 at 8:19
can you type the exact formula that you are trying to put. I mean how would you have entered in an Excel Cell — Siddharth Rout
– Siddharth Rout, Commented Sep 10, 2014 at 8:22
Your VBA is referencing D but your excel formula is referencing E? — Siddharth Rout
– Siddharth Rout, Commented Sep 10, 2014 at 8:29

teylyn · Accepted Answer · 2014-09-10 20:38:13Z

1

you need to use the & character to concatenate the formula's text with the q variable

 Cells(q, 4).Formula = "=IF(ISNA(VLOOKUP(D" & q & ";Lagerplätze!A:B;2;FALSE));"""";VLOOKUP(D" & q & ";Lagerplätze!A:B;2;FALSE))"

Edit: Added missing closing bracket.

edited Sep 10, 2014 at 20:38

answered Sep 10, 2014 at 8:27

teylyn

36.2k6 gold badges58 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

teylyn Over a year ago

@SiddharthRout ... and I even know what Lagerplätze is without consulting a dictionary :-))

Siddharth Rout Over a year ago

@KaChiing: Replace "D" with "E"

Ka Chiing Over a year ago

thanks for the hint, but it still tells me I got an objectrelated error

teylyn Over a year ago

The IF statement is missing a closing bracket. It did in your original code and I did not catch it, because I was distracted by the missing & operator.

Rory · Accepted Answer · 2014-09-10 09:25:05Z

0

The .Formula property requires you to use US regional settings, so:

Cells(q, 5).Formula = "=IF(ISNA(VLOOKUP(E" & q & ",Lagerplätze!A:B,2,FALSE)),"""",VLOOKUP(E" & q & ",Lagerplätze!A:B,2,FALSE)"

Also, if you're using Excel 2007 or later, I'd suggest IFERROR:

Cells(q, 5).Formula = "=IFERROR(VLOOKUP(E" & q & ",Lagerplätze!A:B,2,FALSE),"""")

answered Sep 10, 2014 at 9:25

Rory

34.2k5 gold badges40 silver badges42 bronze badges

4 Comments

Ka Chiing Over a year ago

thanks, that was definitely one of the mistakes! but still, the cell isn't showing the formula like it's supposed to, but only a "FALSE"..

Rory Over a year ago

Just noticed you are putting the formula in column E, but you're also looking up column E. You can't do that - it creates a circular reference.

Ka Chiing Over a year ago

thats not the problem, it is supposed to fill D, by looking up column E. typo; i updated the code

Rory Over a year ago

You adjusted the code wrongly and doubled up the Cells(q, 4).Formula = part!

Collectives™ on Stack Overflow

Writing a "dynamic" formula in a cell

2 Answers 2

4 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related