store formula in cell and use cell ref in vba code

[DBus]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Is it possible to store a formula in a cell and then ref to that cell in the macro code, instead of hard coding the formula into the macro. <o></o>
<o></o>
Very simply, I want to use a variation of the following code to populate a table, but rather than have to look up the formula within the code to make changes, I would like it taken from a cell, where it is more visible. That is store the sumproduct formula below in, say, the cell B1 and refer to B1 in the code.<o></o>
<o></o>
Sub Macro1()
Dim i As Integer
For i = 3 To 14<o></o>
Cells(i, 2).FormulaR1C1 = "=SUMPRODUCT((Validity=RC[-1])*(In=1)*(Out=1))"
Cells(i, 2).Value = Cells(i, 4).Value
Next i<o></o>
End Sub <o></o>
<o></o>
Hope this makes sense.<o></o>
DBus

 

[Andrew Poulsom]

There's rather a lot to digest here. Can you just create a small example macro and give details if the formula please?

I would put the formula in the first cell then copy it to the remaining cells.

 

[DBus]

Sorry - you can tell I am a learner to this forum.

In the code below, I am using the .Formula line to enter the formula into cell J14. The formula needs to look at cell J1 and J2 for specific and unique values to give the right result. These values change in each column. To fill in K14 is relatively easy, the formula will look correctly at cell K1 and K2. However to use the line of code in AC14 or any other cell not adjacent to J14 does not appear so easy. To do this I have put together the more long winded code below.
I am interested to know if there is a simpler way, which requires less lines of repetative code.

Thanks for your continued interest.

'' Cells J14, L14, N14 and P14 repeated from AC14 and AV14
'' Would like to remove need to repeat code
TbBm = Range("E14").End(xlDown).Row - 14 'Last row in Dest table
Set MyCell2 = AnchorCell.Offset(6, 5)
With MyCell2
.Formula = "=" & MyF.Offset(4, 0).Value
.Copy
.Offset(0, 2).PasteSpecial Paste:=xlPasteFormulas
ActiveCell.Copy
ActiveCell.Offset(0, 2).PasteSpecial Paste:=xlPasteFormulas
ActiveCell.Copy
ActiveCell.Offset(0, 2).PasteSpecial Paste:=xlPasteFormulas
End With
Set MyHder2 = Range(MyCell2, MyCell2.Offset(0, 6))
With MyHder2
.Copy
.Offset(0, 19).PasteSpecial Paste:=xlPasteFormulas
.Offset(0, 38).PasteSpecial Paste:=xlPasteFormulas
End With

Thanks
Dbus

 

[Andrew Poulsom]

You still haven't said what the formula is. Why are you using ActiveCell when you haven't activated it? You can put your PasteSpecial in a Step 2 loop using the counter for the column offset.

 

[DBus]

Hello again,
I don't know why I am using the Activecell, other than because it works. In my mind it was referring to MyCell2, but maybe I don't need to use the term. Is that so?

Would you mind giving me a tip about how to do the 'Step 2 loop using the counter for the column offset'? I have treid a few times from examples on the net, but don't seem to get one to work.

Beginner
DBus

 

[Andrew Poulsom]

Try:

    With MyCell2
        .Formula = "=" & MyF.Offset(4, 0).Value
        .Copy
        For i = 2 To 6 Step 2
            .Offset(0, i).PasteSpecial Paste:=xlPasteFormulas
        Next i
    End With

 

[DBus]

Hello,
Many thanks for your help on both of these queries. I have included the piece of code in the script and it works a treat and looks a lot tidier.
Sorry I have been so long in sending through my thanks. I am away quite a bit at the moment.

DBus