CODEWITHSUNDEEP
excelvbaformulaevaluate
Kurt
Kurt

Reputation: 1897

In Excel and VBA, how to set value of cell to the formulas result rather than the formula

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?

Upvotes: 1

Views: 31277

Answers (1)

mkingston
mkingston

Reputation: 2708

Use:

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"

Edit:

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

Upvotes: 5

Related Questions