Referencing Variables in Formulas

[eblytopia]

Hey guys can someone give me an extensive lesson on how to reference
Just Rows, Just Columns or Cell objects in a FormulaR1C1 Formula. I Really need help with it, and I hope to display it in traditional "A1" format Here is an example

'Type Ref in C8 of a blank work book
'and type random numbers from c9:E9


Dim TestIDC As String, TestIDR As String
Sub add()
ActiveSheet.Cells.Select
Selection.Find(What:="Ref", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).Select


TestIDC = Selection.Column
TestIDR = Selection.Row
Range("F9").Select
ActiveCell.FormulaR1C1 = "=sum(" & TestIDC & TestIDR & " :rc[-1])"


End Sub

I am hoping for the activecell(F9) to display =sum(C9:E9) in the cell

any Ideas?

 

[JLGWhiz]

Hey guys can someone give me an extensive lesson on how to reference
Just Rows, Just Columns or Cell objects in a FormulaR1C1 Formula. I Really need help with it, and I hope to display it in traditional "A1" format Here is an example

'Type Ref in C8 of a blank work book
'and type random numbers from c9:E9


Dim TestIDC As String, TestIDR As String
Sub add()
ActiveSheet.Cells.Select
Selection.Find(What:="Ref", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).Select


TestIDC = Selection.Column
TestIDR = Selection.Row
Range("F9").Select
ActiveCell.FormulaR1C1 = "=sum(" & TestIDC & TestIDR & " :rc[-1])"


End Sub

I am hoping for the activecell(F9) to display =sum(C9:E9) in the cell

any Ideas?

This:
ActiveCell.FormulaR1C1 = "=sum(" & TestIDC & TestIDR & " :rc[-1])"

Translates to:
ActiveCell.FormulaR1C1 = "=sum(38 :rc[-1].Value)"

C8 was the selected range on which the variables TestIDC and TestIDR were based.
TestIDC = 3
TestIDR = 8

Neither of these represent a cell ro range and that makes the Sum formula fail because VBA cannot interpret what is wanted.

There is no short explanation of how to use object variables in code that will translate to useable characters in a formula that is to be inserted on a worksheet. It would probably be better if you looked for a tutorial on the web or obtained a book on the subject. There are some good suggestions and offers on the MrExcel Store page.

 

[eblytopia]

Thank you for your response, I actually have the Mr.Excel VBA book. If you can reference any pages that might help me, you are more than welcomed to!!