Trouble with Setting Formula of a Cell on the non active sheet

[fchurchill]

I am having trouble setting the formula of a cell on the non-active sheet. I have tried making the sheet.cell active and then setting the activecell.formula parameter with no luck. The place where my code is hanging up is bold.


Sub MoveFinVIZ()


Dim nRow As Integer
Dim oRow As Integer
Dim columnsearch As Integer
Dim ticker As String
Dim hyper As String

nRow = 2

'find open column on FINVIZ sheet
For columnsearch = 1 To 100

If Sheets("FINVIZ").Cells(1, columnsearch) = "" Then

Exit For
End If

Next columnsearch

'insert date
Sheets("FINVIZ").Cells(1, columnsearch).Value = Date
Sheets("FINVIZ").Cells(1, columnsearch).NumberFormat = "mm/dd/yy"


'Loop through the contents on alltimehigh sheet one Row at a time
For oRow = 18 To 100

'Get the ticker and store it as a variable.
ticker = Sheets("DAILY").Cells(oRow, 14)

If ticker <> "" Then

'Set the ticker in FINVIZ
'Sheets("FINVIZ").Cells(nRow, columnsearch) = ticker
hyper = "http://finance.yahoo.com/echarts?s=" & ticker
Sheets("FINVIZ").Cells(nRow, columnsearch).Select
ActiveCell.FormulaR1C1 = "=HYPERLINK(""" & hyper & """,""" & ticker & """)"
nRow = nRow + 1
End If

Next oRow

End Sub

 

[fchurchill]

Finally got it.

hyper = "http://finance.yahoo.com/echarts?s=" & ticker & "+Interactive#{" & Chr(34) & "showArea" & Chr(34) & ":false," & Chr(34) & "showLine" & Chr(34) & ":false," & Chr(34) & "showCandle" & Chr(34) & ":true," & Chr(34) & "lineType" & Chr(34) & ":" & Chr(34) & "candle" & Chr(34) & "," & Chr(34) & "range" & Chr(34) & ":" & Chr(34) & "max" & Chr(34) & "," & Chr(34) & "allowChartStacking" & Chr(34) & ":true}"
Sheets("FINVIZ").Cells(nRow, columnsearch) = hyper



With Worksheets("FINVIZ")
.Hyperlinks.Add Anchor:=.Cells(nRow, columnsearch), _
Address:=hyper, _
TextToDisplay:=ticker
End With