How to write VBA for Excel function?

[VAer]

For example, Activesheet.Cells(15, 18).Value = "MSFT", where 18 is column R

I would like to write ActiveSheet.Cells(16, 18).Formula = "=STOCKHISTORY(ActiveSheet.Cells(15, 18).Value,TODAY()-30,TODAY())", something like that, how to write syntax correctly? There is error in my code, it turns out to be =@STOCKHISTORY(@ActiveSheet.Cells(15, 18).Value,TODAY()-30,TODAY()) Actually, I want Cell R16 turn out to be = STOCKHISTORY("MSFT",TODAY()-30,TODAY()), but I cannot use MSFT directly, how to make Activesheet.Cells(15, 18).Value as part of code.

I need to write it in this way, since there are a list of companies, I have to write a VBA loop. Cells(15, 18), Cells(15, 21), Cells(15, 24), Cells(15, 27), ........

To make my question clear: At the end, it should look something similar to attached screenshot, and I would like to write the function =STOCKHISTORY(ActiveSheet.Cells(15, 18).Value,TODAY()-30,TODAY()) in VBA code. How?

Thanks.

[kvsrinivasamurthy]

Try this

"=STOCKHISTORY(R15,TODAY()-30,TODAY())"

[TMS]

Try:

Please Login or Register  to view this content.

[rorya]

Something like:

Please Login or Register  to view this content.

[VAer]

Try:

Please Login or Register  to view this content.

It does not work. It ends up with =@STOCKHISTORY(@MSFT,TODAY()-30,TODAY()) , as I mentioned in my post. The cell appears as #NAME?

[rorya]

You need to add quotes if you're going to do it that way:

Please Login or Register  to view this content.

[VAer]

Try this

"=STOCKHISTORY(R15,TODAY()-30,TODAY())"

First, it does not work (I tried one cell only). Secondly, I don't want to use R15, I would like to use ActiveSheet.Cells(15, 18).Value. Eventuallly, it will be a For Loop

[VAer]

Something like:

Please Login or Register  to view this content.

Thanks. But I don't understand the code R1C1/R1C5. Secondly, I don't know how many columns there. I have a list of companied, LastRowC = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row, the number of companies = LastRowC - 1 (heading title row), then I put each symbol in the cell Activesheet.Cells(15, 18), Activesheet.Cells(15, 21), Activesheet.Cells(15, 24)......

[VAer]

You need to add quotes if you're going to do it that way:

Please Login or Register  to view this content.

Thanks for correction, it is """" for using double quote in VBA. I have tried this one, it turns out better, but still not right. It ends up with =@STOCKHISTORY("MSFT",TODAY()-30,TODAY()) the cell appears as Date but no data

There is something wrong with syntax, it already turns out to have @ as result

[rorya]

Use .Formula2 instead of .Formula

[VAer]

Use .Formula2 instead of .Formula

Thanks. It seems below code working.

ActiveSheet.Cells(16, 18).Formula2 = "=STOCKHISTORY(" & """" & ActiveSheet.Cells(15, 18).Value & """" & ",TODAY()-30,TODAY())"

[bakerman2]

Eventuallly, it will be a For Loop

Something like this maybe.

Please Login or Register  to view this content.