VBA how to copy a cell value and not the formula

P

pierre brice

I have a function that needs a sumif which is using as criteria the cell
value from same column . I use a loop over several column, that is to say my
criteria is changing from column to column. that is why I would need that my
criteria for the sumif function would fix on the cell value and not on the
formula .
can you help me.

example
For i = 3 To 40 ' i are the lines
For col = 3 To 9 'col are the columns
'Sheets("page").Cells(i, col).Formula = "=SUMIF(Cells(1,7):Cells(1,
84),""=Cells(1, col)"",Cells(3;10):Cells(10;100))"

Next col


it does not check the value in Cells (1, col) but compares the value to
"=Cells(1, col)"
 
J

Jim Thomlinson

Your formula has issues. The Range and SumRange are not the same size? What
were you wanting to sum up??? This will be closer...

Sheets("page").Cells(i, col).Formula = "=SUMIF(G1:CF1,""=" & Cells(1,
col).Value & """,G2:CF2)"
 

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

Dealing with the error: unable to filter on excel hc home school 0
VBA to run all variable combinations in a calculation model and store result 6
Loop Through Text and Test for Scenarios Q 31
Fill both cells if one cell has a negative amount 4
Six columns randon no.'s no dupes 6
A For Each in Range... with a .Find(what:="TJ", ... in it problem 15
Shed some light on this WS_Calculate macro. 3
import text 1

Top