Variable used in a =SUMIF() function?

C

circuit_breaker

Hi,

How do I replace the "A40" parameter with the nLastrow varable below?

nLastrow = 100

objExcel.ActiveSheet.Range("D20").Formula = "=SUMIF
(A4:A40,"">'5734' "",B4:B40)"

I've been trying many things with double-double quotes but nothing
works.

Thanks.
 
C

circuit_breaker

Hi,

How do I replace the "A40" parameter with the nLastrow varable below?

  nLastrow = 100

  objExcel.ActiveSheet.Range("D20").Formula = "=SUMIF
  (A4:A40,"">'5734' "",B4:B40)"

I've been trying many things with double-double quotes but nothing
works.

Thanks.
Click to expand...

This worked:

ActiveSheet.Range("D20").formula="=SUMIF(A4:A"&nRow
&","">'5734'"",B4:B41)"
 
B

Bob Phillips

objExcel.ActiveSheet.Range("D20").Formula = _
"=SUMIF(A4:A" & nLastRow & ","">'5734' "",B4:B" & nLastRow & ")"
 
D

Dave Peterson

I'm not sure what you're doing, but even though excel is forgiving, I'd want my
ranges to be the same size. And are you sure you want those single quotes
around that 5734?

Just in case you want to try it:

ActiveSheet.Range("D20").Formula _
= "=SUMIF(A4:A" & nRow & ","">5734"",B4:B" & nRow & ")"
 
Top