Formula Help within Code
 

Hello
I am using the following to insert a formula in a cell...

Range("k13").Formula = "=SUMPRODUCT(--($d$14:$d$500=""7-F""),--($J
$14:$J$500=""A""),--($i$14:$i$500<""""),--(ISERROR(MATCH($c$14:$c
$500,MyFile.xlsm'!Data,0))),roundup(1.02*(k$14:k$5 00),0))"

Where Data is a named range with MyFile.xlsm, and the macro resides in
same. The problem is that the file is renamed occasionally and then
the reference in the formula is not correct. I am already using
Dim basebook As Workbook
Set basebook = ThisWorkbook

In the code and I believe that using basebook would remedy the
problem. Hoever I am not sure how to apply Basebook to my formula.

Thanks in advance

Formula Help within Code
 

Try:

Range("k13").Formula = "=SUMPRODUCT(--($d$14:$d$500=""7-F"")," _
& "--($J$14:$J$500=""A"")," _
& "--($i$14:$i$500<"""")," _
& "--(ISERROR(MATCH($c$14:$c$500,'" & thisworkbook.name & "'!Data,0)))," _
& "roundup(1.02*(k$14:k$500),0))"

(And watch your apostrophes. You were missing the leading apostrophe in your
original post.)




On 06/18/2010 10:25, wrote:
Hello
I am using the following to insert a formula in a cell...

Range("k13").Formula = "=SUMPRODUCT(--($d$14:$d$500=""7-F""),--($J
$14:$J$500=""A""),--($i$14:$i$500<""""),--(ISERROR(MATCH($c$14:$c
$500,MyFile.xlsm'!Data,0))),roundup(1.02*(k$14:k$5 00),0))"

Where Data is a named range with MyFile.xlsm, and the macro resides in
same. The problem is that the file is renamed occasionally and then
the reference in the formula is not correct. I am already using
Dim basebook As Workbook
Set basebook = ThisWorkbook

In the code and I believe that using basebook would remedy the
problem. Hoever I am not sure how to apply Basebook to my formula.

Thanks in advance

--
Dave Peterson