Take the 2-minute tour ×

I'm getting values from one sheet and placing them in another using a macro in Excel. I currently have this which works fine:

sheet.range("B2:B35").Value = "=IF(SUMPRODUCT(--(Raw!$B$11:$B$322=$A2),--(Raw!$D$11:$D$322=All!$B$2),Raw!$H$11:$H$322)<>0,SUMPRODUCT(--(Raw!$B$11:$B$322=$A2),--(Raw!$D$11:$D$322=All!$B$2),Raw!$H$11:$H$322),""-"")"

It, obviously, puts that entire formula as the value of the cell. What I'd like is it just to put the result of the formula into the cell. I've tried adding Evaluate() around the "IF..." part, but then the IF doesn't evaluate correctly (I just end up with "-" in each cell). Is this possible to do or do I have to have separate code to loop through and change the value to the value of the cell?

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted


sheet.range("B2:B35").Formula = "Your formula here"

If that doesn't work you may have to change the formatting (do this first):

sheet.range("B2:B35").NumberFormat = "General"


A solution turned out to be addition of the following line after the OP's code:

sheet.range("B2:B35").value = sheet.range("B2:B35").value
share|improve this answer
The original code was already using .Formula instead of .Value (which I tried both when using Evaluate). That didn't change what was placed into the cells. The formatting on the cells is already General, although that seems to have been automatic; I didn't do set it specifically through the code. –  Braffin Oct 4 '12 at 23:59
Weird. If you run it, then select it and do the following (in the immediate window): Selection.NumberFormat = "General", then Selection.Value = Selection.Value, what do you get? –  mkingston Oct 5 '12 at 0:07
Modifying what you said slightly: adding sheet.range("B2:B35").Value = sheet.range("B2:B35").Value made it just a number. That works then. I was just hoping I wouldn't have to do this as I have the code in the OP about 5 more times in a loop. Oh well. Easy enough to add 1 more line of code. –  Braffin Oct 5 '12 at 0:14
Very odd. Oh well, glad your problem's solved :) –  mkingston Oct 5 '12 at 0:33
add comment

Your Answer


