Run Time Error 1004 when running VBA

[bossOnothin]

Hi, I am trying to scrape data from Yahoo Finance, and the code works fine when there is a defined URL. However, when I added a variable, it gives me error 1004. Here is my code:

Sub macro()

Cells.Clear

ticker = Sheets(1).Cells(1, k)

qurl = "https://finance.yahoo.com/quote/" & ticker & ""

With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=Range("A27"))
.BackgroundQuery = True
.Refresh BackgroundQuery:=True
End With


End Sub

 

[StephenCrump]

Welcome to the Forum!

What is k? Trying to access .Cells(1, 0) will give you a run-time error 1004.

 

[bossOnothin]

Welcome to the Forum!

What is k? Trying to access .Cells(1, 0) will give you a run-time error 1004.

My plan is to be able to type a ticket symbol in cell K1 and extract data from the relevant Yahoo Finance page. How else should I reference cell K1?

 

[StephenCrump]

You can refer to cell K1 as .Range("K1"), or .Cells(1,"K") or .Cells(1,11)

But .Cells(1,k) refers to row 1, column k where k is a variable, for which you haven't set a value hence it is empty.

I recommend you use Option Explicit with all your code. This would give you a compile error: variable not defined, letting you know that your syntax for k was not correct.

 

[bossOnothin]

You can refer to cell K1 as .Range("K1"), or .Cells(1,"K") or .Cells(1,11)

But .Cells(1,k) refers to row 1, column k where k is a variable, for which you haven't set a value hence it is empty.

I recommend you use Option Explicit with all your code. This would give you a compile error: variable not defined, letting you know that your syntax for k was not correct.

Thank you.

One more thing, when I type a new ticker it does not update. How do I make it so that it auto refreshes when I update K1?