String formulas to range of cells

[l_eonandr]

Please help;


I have 7 lines of code setting a variable to a formula, first 3 which vb accepts and
the last 4 are rejects with the

runtime error 13
type missmatch.


Can anyone please help me understand what I am missing here with the last 4 lines of code?
All are variables are dim as variants

Dim strFormulas(1 To 2) As Variant '(This would allow to set 2 formulas to populate 2 columns in details sheet)
Dim strFormulas1(1) As Variant
Dim strFormulas2(1 To 4) As Variant '(This allows to set 4 formulas to populate 4 columns in Wells sheet)
    

    strFormulas(1) = "='Wells'!K2"


    strFormulas(2) = "='Wells'!J2"


    strFormulas1(1) = "=SUM(INDEX(Wells!A:AJ,0,MATCH(Summary!B9,Wells!A$1:AJ$1,0)))"


    strFormulas2(1) = "=(SUMIFS($K:$K,$E:$E,$E2,$J:$J,T$1,$H:$H, & Chr(34) & " & Chr(34) & [>=] & 400 & Chr(34) & " & Chr(34) & ,$H:$H, & Chr(34) & " & Chr(34) & [<] & 500 & Chr(34) & " & Chr(34) & )) & [+] & (SUMIFS($K:$K,$E:$E,$E2,$J:$J,T$1,$H:$H, & Chr(34) & " & Chr(34) & [=] & 40 & 
[*] & Chr(34) & " & Chr(34) & ))"


    strFormulas2(2) = "=SUMIFS($K:$K,$E:$E,$E2,$J:$J,T$1,$H:$H, & """ & [>=] & 300 & """ & ,$H:$H, & """ & [<] & 400 & """ & )"


    strFormulas2(3) = "=SUMIFS($K:$K,$E:$E,$E2,$J:$J,T$1,$H:$H, & """ & [>=] & 200 & """ & ,$H:$H, & """ & [<] & 300 & """ & )"


    strFormulas2(4) = "=SUMIFS($K:$K,$E:$E,$E2,$J:$J,T$1,$H:$H, & """ & [>=] & 100 & """ & ,$H:$H, & """ & [<] & 200 & """ & )"

 

[RickXL]

Hi,

I think the last one should be like this:

strFormulas2(4) = "=SUMIFS($K:$K,$E:$E,$E2,$J:$J,T$1,$H:$H,"">=100"",$H:$H,""<200"")"

 

[l_eonandr]

Hi,

I think the last one should be like this:

strFormulas2(4) = "=SUMIFS($K:$K,$E:$E,$E2,$J:$J,T$1,$H:$H,"">=100"",$H:$H,""<200"")"

Thanks so much, I thought I had tried that but when I copied your line and inserted it, passed the run trial.
I'll make the same changes to the other lines and see if that fixes all the problems.
How do I give you due credit, didn't see anything to click on to give points or anything?
Really appreciate the help.

 

[RickXL]

No problem.

I only did one - I was feeling lazy. Sorry.

If I have something like that and I need to work it out then I start from the formula in Notepad then work back to what the string has to be. For example:

=SUMIFS($K:$K, $E:$E,$E2, $J:$J,T$1, $H:$H, ">=100",$H:$H,"<200")

"=SUMIFS($K:$K, $E:$E,$E2, $J:$J,T$1, $H:$H, "">=100"",$H:$H,""<200"")"

In this case it was just a matter of adding a quote to both ends then doubling up the quotes in the formula.


We do not have a "solved" option or a points option. The best you can do is "like" with the link in the bottom right hand corner.
Every time I get a "like" my pay is doubled.

Regards,