Write a formula in Excel VBA

Question

I'm struggling to write a formula in Excel VBA.

This is my code:

Dim Formula as Range
Dim Number as Integer

Number = 1

Formula.formula = "=MAX(IF(LEFT(B7:B250,1)= & Number &, B7:B250))"

It gives a syntax error.

Does this answer your question? [How can I insert variable into formula in VBA](https://stackoverflow.com/questions/42503316/how-can-i-insert-variable-into-formula-in-vba) — BigBen, Jan 28 '20 at 13:33
You are missing some quotes: `"=MAX(IF(LEFT(B7:B250,1)=" & Number & ",B7:B250))"`. — BigBen, Jan 28 '20 at 13:33

score 1 · Accepted Answer · answered Jan 28 '20 at 13:37

1

You are missing some quotes:

"=MAX(IF(LEFT(B7:B250,1)=" & Number & ",B7:B250))"

Also, this is an array formula so use .FormulaArray instead of .Formula.

answered Jan 28 '20 at 13:37

BigBen

Seems like I still get error: run-time: 91. Im looking for it – ChangeWorld Jan 28 '20 at 13:40
1

You need to `Set` your range. (Also, use a different variable name than `Formula`). – BigBen Jan 28 '20 at 13:41
Yess it worked, but `debug.print Formula` does not work??? I would like to know what is inside – ChangeWorld Jan 28 '20 at 13:45
1

What are you trying to `Debug.Print`? Should be `Debug.Print "=MAX(IF(LEFT(B7:B250,1)=" & Number & ",B7:B250))"`? – BigBen Jan 28 '20 at 13:46
Ah there is not a result inside Formula? I mean after we did `.FormulaArray` is there not a result inside? – ChangeWorld Jan 28 '20 at 13:49
1

Oh you can `Debug.Print` the `Formula` or `FormulaArray` too. – BigBen Jan 28 '20 at 13:52
https://stackoverflow.com/questions/60095600/write-properly-a-formula-in-excel-vba Can you look at this question? – ChangeWorld Feb 06 '20 at 13:00

1 Answers1