Loop Through Cells to Create Formula

[twoodsmall79]

I am looking for a way to create a formula that is to be housed in a cell but will vary length depending on the number of components. Basically I have a Display sku followed by x number of components. I am looking for a formula similar to "= (CompUnits1*Price1)+(CompUnits2*Price2)..."

I have tried creating a string but am running into troubles. This is in the middle of some other code but basically here is what I have. Any help is appreciated.

Dim Formula as String
Dim DispComp, MyDisp as Range

Formula = BasePrice
        For Each DispComp In MyDisp.Offset(1, -1).Resize(l)
            Formula = Formula & "+ (" & DispComp.Offset(0,6) & "*" & DispComp.(Offset(0,12) & ")"
        Next DispComp

 

[Scott Huish]

Why not just use Sumproduct?

 

[FDibbins]

is this info listed 1 below the other?
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

1​

[/td][td]QTY[/td][td]Cost[/td][td][/td][/tr]

[tr][td]

2​

[/td][td]

1​

[/td][td]

10​

[/td][td]

550​

[/td][/tr]

[tr][td]

3​

[/td][td]

2​

[/td][td]

20​

[/td][td][/td][/tr]

[tr][td]

4​

[/td][td]

3​

[/td][td]

30​

[/td][td][/td][/tr]

[tr][td]

5​

[/td][td]

4​

[/td][td]

40​

[/td][td][/td][/tr]

[tr][td]

6​

[/td][td]

5​

[/td][td]

50​

[/td][td][/td][/tr]
[/table]
C2=SUMPRODUCT(A2:A6*B2:B6)

 

[twoodsmall79]

Thanks to both of you for pointing me in the right direction. I am not able to do this as a formula in the worksheet itself as I am importing a number of items at once but I was able to get the below code to work perfectly.

Dim RngA, RngB, Display as Range
Dim Ans as Double
Dim l as long

Set RngA = Range(Display.Offset(1, 6), Display.Offset(l, 6))
        Set RngB = Range(Display.Offset(1, 13), Display.Offset(l, 13))
        Ans = Application.SumProduct(RngA, RngB)

The Display and l variables were identified earlier in the code.

Thanks again for your help. This site always help point me in the right direction.