PDA

View Full Version : Solved: assigning a formula to a cell



CatDaddy
06-08-2011, 11:05 AM
im trying to put formulas in a series of cells and the criteria comes from two string arrays (rowHeader and colHeader) but im getting a 1004 runtime error:


Dim count As Integer, count2 As Integer
Dim formulaStr As String
For count2 = 2 To 7
For count = 2 To 31
formulaStr = "=SUMPRODUCT((SourceSheet!D:D=" + rowHeader(count) + ")*(SourceSheet!E:E=" + colHeader(count2) + "))"
Cells(count, count2).Value = formulaStr
Next count
Next count2



:banghead:

Bob Phillips
06-08-2011, 11:34 AM
Try



Dim count As Integer, count2 As Integer
Dim formulaStr As String
For count2 = 2 To 7
For count = 2 To 31
formulaStr = "=SUMPRODUCT((SourceSheet!D:D=" & rowheader(count) & ")*(SourceSheet!E:E=" & colheader(count2) & "))"
Cells(count, count2).Value = formulaStr
Next count
Next count2

CatDaddy
06-08-2011, 11:35 AM
i think the problem is that rowHeader(count) doesnt get added to the formula as "Criteria" but as Criteria, but when i try to add an extra " to the formula, it just sets each cell to:

=SUMPRODUCT((RAW_Incoming!D:D=" & rowHeader(count) & ")*(RAW_Incoming!E:E=" & colHeader(count2) & "))

p45cal
06-08-2011, 01:11 PM
Does this do it for you?:
formulaStr = "=SUMPRODUCT((SourceSheet!D:D=""" & rowHeader(count) + """)*(SourceSheet!E:E=""" + colHeader(count2) + """))"
Cells(count, count2).Formula = formulaStr

CatDaddy
06-08-2011, 01:43 PM
Does this do it for you?:
formulaStr = "=SUMPRODUCT((SourceSheet!D:D=""" & rowHeader(count) + """)*(SourceSheet!E:E=""" + colHeader(count2) + """))"
Cells(count, count2).Formula = formulaStr


you rock my world