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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.