astoned
astoned

Reputation: 28

VBA creating formulas referencing a range

After several hours of research, I still can't solve what seems to be a pretty simple issue. I'm new to VBA, so I will be as specific as possible in my question.

I'm working with a DDE link to get stock quotes. I have managed to work out most of the table, but I need a VBA to create a finished formula (i.e., without cell referencing) in order to the DDE link to work properly.

My first code is as follows:

    Sub Create_Formulas()

    Range("J1").Formula = "=Trade|Strike!" & Range("A1").Value

    End Sub

Where J2 is the blank cell and A2 contains the stock ticker. It works fine, but when I try to fill out the rows 2 and bellow, it still uses A1 as a static value.

    Sub Create_Formulas()

    Dim test As Variant
    ticker = Range("A1").Value
    'Test to make variable change with each row

    'Range("J1:J35").Formula = "=Trade|Strike!" & Range("A1:A35").Value
    'not working

    Range("J1:J35").Formula = "=Trade|Strike!" & ticker
    'not working

    End Sub

I couldn't find a way to solve that, and now I'm out of search queries to use, so I'm only opening a new topic after running out of ways to sort it by myself. Sorry if it is too simple.

Upvotes: 0

Views: 234

Answers (2)

TomW
TomW

Reputation: 68

Firstly, in your second set of code, you define a variable "test", but never give it a value. You assign a value to the variable "ticker", and then never reference it.

Secondly, the value you have assigned to ticker is a static value, and will not change when it is entered in a different row.

Thirdly, I think your issue could be solved with a formula in Excel rather than VBA. The "INDIRECT" function can be quite useful in situations like this. Try inserting the formula

=INDIRECT("'Trade|Strike'!"&A1)

into cell A1, then copy down.

Note the ' ' marks around "Trade|Strike". This is Excels syntax for referencing other sheets.

Upvotes: 0

Luuklag
Luuklag

Reputation: 3914

You are referencing absolute cell adresses here. Like you would do when using $A$1 in a normal excel formula.

What you want to do is:

Dim row as Integer
For row = 1 to 35
  Cells(row,10).Formula = "=Trade|Strike!" & Cells(row,1).Value
Next row 

This will fill the range J1 to J35 with the formula. Since (row,10) indicates the intersection of row and column 10 (J)

Upvotes: 1

Related Questions