Excel VBA insert formula using variables

Question

I am trying to insert a formula into a cell with a help of VBA and all of the time I receive error 1004: "Application-defined or object-defined error". Where does the problem lie?

Worksheets("Sheet1").Range("L10").Formula = "=VLOOKUP(" & Cells(6, 15) & ", SQLTable, 2, 0)*" & Cells(6, 16)

First Debug.Print "=VLOOKUP(" & Cells(6, 15) & ", SQLTable, 2, 0)*" & Cells(6, 16) and see how the result differs to the formula you would just write manually in the cell. — BigBen
– BigBen, Commented May 27, 2020 at 20:17
You may also want to qualify your Cells object (both instances) with a worksheet. Sheets("Sheet1").Cells(6, 15) etc.... or even better, create a worksheet variable for better readability — urdearboy
– urdearboy, Commented May 27, 2020 at 20:19
@BigBen I tried Debug.Print "=VLOOKUP(" & Cells(6, 15) & ", SQLTable, 2, 0)*" & Cells(6, 16) and got what actually was expected =VLOOKUP(06.01.2020, SQLTable, 2, 0)*-1000. This is exactly what it would look like, if I write it manually. — Msoul
– Msoul, Commented May 27, 2020 at 20:28
I'm not sure that's right... that 06.01.2020 would not work in a formula like that. — BigBen
– BigBen, Commented May 27, 2020 at 20:28

urdearboy · Accepted Answer · 2020-05-27 20:24:57Z

2

I believe you want the address of Cells(6, 15) since you are dropping the formula on the sheet. Also amended to include a worksheet variable for proper object qualification

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
ws.Range("L10").Formula = "=VLOOKUP(" & ws.Cells(6, 15).Address & ", SQLTable, 2, 0)"

This will default to the absolute address ($O$6). If you want to remove the columns being locked you can ammend the absolute properties (Column Absolute, Row Absolute) like so: ws.Cells(6, 15).Address (False, False)

answered May 27, 2020 at 20:24

urdearboy

14.6k6 gold badges35 silver badges64 bronze badges

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

7 Comments

Msoul Over a year ago

Thanks! Works perfectly! So the problem was that vba used the value in the cell and not the address as it would be correct for such formula?

urdearboy Over a year ago

Correct. You just need to tack on the multiplication part at the end using the same method (access the address that stores the value instead of the value)

BigBen Over a year ago

... and of course, you can do "=VLOOKUP(O6, SQLTable, 2, 0)"

Msoul Over a year ago

Sure! Thank you again :) Spent much more time than was needed for such a task :/

urdearboy Over a year ago

Yea, if you are using a static range then you may want to consider @BigBens comment. No point in beating around the bush with references when you could directly state the address. The current method would make more sense if you plan to sub out those row/column indexes with variables

|

Collectives™ on Stack Overflow

Excel VBA insert formula using variables

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related