Formula Help within Code

S

sailor4life61

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$500),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
 
D

Dave Peterson

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.)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top