Excel VBA "formula" function

Question

I have a problem with the Excel VBA Formula function. I want my VBA sub to generate the following formula in a cell:

=VLOOKUP(C5;data!J6:K611;2;TRUE)

Therefor, I used the following VBA line:

Sheets("test").Cells(1, 1).Formula = "=VLOOKUP(C5;data!J6:K611;2;TRUE)"

This results in an error: "Run-time error 1004: Application-defined or object-defined error"

If I use the following statement and afterwards manually add the "=" in front of the formula, the functions works well:

Sheets("test").Cells(1, 1).Value = "VLOOKUP(C5;data!J6:K611;2;TRUE)"

What am I doing wrong?

In principle, that should work fine. To get more insight, enter the formula manually into the spreadsheet, and investigate the value of the property Formula in the immediate window. Maybe you can spot the difference then. — paulroho
– paulroho, Commented Jun 19, 2014 at 20:40

pnuts · Accepted Answer · 2014-06-19 20:42:35Z

4

I think the conversion of delimiters takes place in the spreadsheet rather than in VBA - where you require , rather than ;.

Please try:

Sheets("test").Cells(1, 1) = "=VLOOKUP(C5,data!J6:K611,2,1)"

answered Jun 19, 2014 at 20:42

pnuts

59.7k11 gold badges92 silver badges141 bronze badges

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

1 Comment

pdewit Over a year ago

This indeed solved the issue. So in general, in VBA all arguments of a function should be separated by commas. Only when the function is placed inside an Excel cell using VBA, these commas are converted by Excel to ";". Is this correct?

Collectives™ on Stack Overflow

Excel VBA "formula" function

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related