Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUmif and declared ranges
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&"))" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUmif and declared ranges
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) -- Regards, Tom Ogilvy "marty" wrote in message ... 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&"))" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUmif and declared ranges
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) -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... 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) -- Regards, Tom Ogilvy "marty" wrote in message ... 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&"))" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUmif and declared ranges
xlApp.Range("j28") = "=sumif('CPCT DEC'!" & ColRng & ","" 89298"",'CPCT
DEC'!" & RngRef & ")" -- HTH RP (remove nothere from the email address if mailing direct) "marty" wrote in message ... 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&"))" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumif and Specified Ranges | Excel Worksheet Functions | |||
Reference a variable declared in VBA | Excel Worksheet Functions | |||
Macro for autofilter using variables declared in worksheet | Excel Worksheet Functions | |||
Transfering Declared value through procedures | Excel Programming | |||
Can Not Find declared dll | Excel Programming |