0

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)
BigBen
  • 46,229
  • 7
  • 24
  • 40
Max Hamesoul
  • 3
  • 1
  • 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 May 27 '20 at 20:17
  • 1
    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 May 27 '20 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. – Max Hamesoul May 27 '20 at 20:28
  • I'm not sure that's right... that `06.01.2020` would not work in a formula like that. – BigBen May 27 '20 at 20:28
  • Write `Worksheets("Sheet1").Select` before your code line – Sharif Lotfi May 27 '20 at 21:12
  • @SharifLotfi you should avoid `.Select` just about 100% of the time. This only acts to slow your code down. Take a look at [this 'famous'](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) post for more details on why you should avoid this and what you should do instead :) – urdearboy May 27 '20 at 22:42

1 Answers1

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)

urdearboy
  • 14,439
  • 5
  • 28
  • 58
  • 1
    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? – Max Hamesoul May 27 '20 at 20:37
  • 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)* – urdearboy May 27 '20 at 20:37
  • 1
    ... and of course, you can do `"=VLOOKUP(O6, SQLTable, 2, 0)"` – BigBen May 27 '20 at 20:42
  • Sure! Thank you again :) Spent much more time than was needed for such a task :/ – Max Hamesoul May 27 '20 at 20:42
  • 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 – urdearboy May 27 '20 at 20:46
  • @urdearboy Yeah, I am going to make a loop which will change the current formula with each iteration, adding the result to other variable, basically like a string concatenation. – Max Hamesoul May 27 '20 at 21:00
  • @urdearboy your solution is perfect, it works for me. thank you. – Sharif Lotfi May 27 '20 at 21:15