Using variable in formula

[mmetzinger]

Ok, need some help in the formula line of the code below I need it to use the stored value instead of a set cell reference but I can't figure out the syntax.

Sub test()

Dim c As Range
Dim R1 As Integer
Dim R2 As Integer
R1 = 2
Dim fillto As String
Dim newa As String

For Each c In Range("A:A")
       If Columns("A").Find(What:="Total", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate Then
        R2 = ActiveCell.Row
        fillto = "D" & R2
        Cells(R1, 4).Select
[COLOR=Red]        ActiveCell.FormulaR1C1 = "=RC[-1]" / fillto[/COLOR]
        Range("D2").Select
        Selection.Autofill Destination:=Range("D2", fillto), Type:=xlFillDefault
        newa = "A" & R2
        R1 = R2 + 1
        Range(newa).Select
        End If
Next c

End Sub

Thanks for any help you can give me!

 

[GlennUK]

ActiveCell.Formula = "=" & fillto

 

[mmetzinger]

Not so much, it just references the new cell as text and not as a cell for comparison

Code
ActiveCell.FormulaR1C1 = "=RC[-1]/" & fillto

generates the following formula
=C2/'C5'

intead of just
=c2/c5

 

[mmetzinger]

I guess what I really need is a line of code that will convert the string value stored in "Fillto" into a range. Any help?

 

[shg]

In plain words, what is the code supposed to do?

 

[mmetzinger]

The data takes the individual values from column C and divides them from the total count that is under each group of them in column C. The issue is I can't set a fixed value for the formula to reference since the number of individual items in each group can change and would thus throw off where the total value to divide by is stored.

 

[shg]

In a little more detail, maybe?

And post something that shows your data layout?

 

[GlennUK]

Not so much, it just references the new cell as text and not as a cell for comparison

Code
ActiveCell.FormulaR1C1 = "=RC[-1]/" & fillto

generates the following formula
=C2/'C5'

intead of just
=c2/c5

You are mixing reference types. Either use Formula with A1 style references or FormulaR1C1 with R1C1 style references. Maybe:

        fillto = "R" & R2 & "C4"

 

[DonkeyOte]

this thread is in effect a dupe, see: http://www.mrexcel.com/forum/showthread.php?t=527156 though things were no clearer there either