SUmif and declared ranges

[Guest]

I am trying to build a sumif equation to be placed into a cell using vba. I
have declared and set "ColRng " and "RngRef" to ranges. when I try to get
out of this equation I keep getting notified that the comma is causing
problems.

Any insight appreciated

xlApp.Range("j28") = "=sumif(('CPCT DEC'!" & ColRng & ")" &,& 89298,"('CPCT
DEC'!" & RngRef&"))"

 

[Tom Ogilvy]

what is the formula you want in the cell after the code runs.

I suspect you want
sSht = "'CPCT DEC'!"
xlApp.Range("J28").Formula = _
"=sumif(" & sSht & colrng.Address & _
",89298," & sSht & RngRef.Address & ")"

so to test out the results using the immediate window with some
representative range assignments:

set colRng = Range("A1:A10")
set RngRef = Range("B1:B10")
? "=sumif(" & sSht & colrng.Address & _
",89298," & sSht & RngRef.Address & ")"

Produces:

=sumif($A$1:$A$10,89298,$B$1:$B$10)

 

[Tom Ogilvy]

Left out the top line in the "test"

sSht = "'CPCT DEC'!"
set colRng = Range("A1:A10")
set RngRef = Range("B1:B10")
? "=sumif(" & sSht & colrng.Address & _
",89298," & sSht & RngRef.Address & ")"

Produces:
=sumif('CPCT DEC'!$A$1:$A$10,89298,'CPCT DEC'!$B$1:$B$10)

 

[Bob Phillips]

xlApp.Range("j28") = "=sumif('CPCT DEC'!" & ColRng & ","" 89298"",'CPCT
DEC'!" & RngRef & ")"

--

HTH

RP
(remove nothere from the email address if mailing direct)