Sum of columns

[chunu]

hi,
I want sum of f2:h2 with the following formula, if i remove .value=.value , formula works fine but with .value=.value
only summing f2:g2
Please help.

With Range("d2").Formula = "=Sum(f2:h2)"
.Value = .Value
     End With

Thanks

 

[My Aswer Is This]

Try this:

Range("D2").Value = Application.WorksheetFunction.Sum(Range("F2:H2"))

 

[Michael M]

Try

Sub MM1()
With Range("d2")
    .Formula = "=Sum(f2:h2)"
    .Value = .Value
End With
End Sub

 

[My Aswer Is This]

What would be the logic behind putting a formula in the cell get the results you want and then remove the formula. Why not use my solution? Just wondering.

Sub MM1()
With Range("d2")
    .Formula = "=Sum(f2:h2)"
    .Value = .Value
End With
End Sub

[/QUOTE]

 

[Michael M]

I agree....would only do it if there was other cells impacted by that formula !!

 

[chunu]

Try this:

Range("D2").Value = Application.WorksheetFunction.Sum(Range("F2:H2"))

Hi,
Thanks for reply
this sum only F2 & G2, H2 not picked
For some reason i want to remove formula after result.
thanks

 

[Peter_SSs]

Hi,
Thanks for reply
this sum only F2 & G2, H2 not picked

The suggestion by My Aswer Is This includes the sum of all 3 cells for me.
BTW, did you also try Michael M's suggestion?

What would be the logic behind putting a formula in the cell get the results you want and then remove the formula. Why not use my solution? Just wondering.

A couple of reasons come to mind for me.

1. The OP was trying to use the idea of formula & replace so the suggestion along those lines indicates what was wrong with the OP's syntax (that is, a learning exercise on that syntax)


2. Relates to coding style/preference. The formula & replace lends itself immediately to the situation of a larger range of sums simply by changing

With Range("D2:D1000")

whereas the WorksheetFunction approach needs to be changed to Formula/Replace or Evaluate or building a loop (which would be considerably slower as the range gets larger)

Note, I am not suggesting that your solution here was wrong or inferior, just answering your query.

 

[chunu]

Hi,
sorry guys it was my mistake , formula was in wrong position.
Thanks you all for your help.

 

[Peter_SSs]

Cheers. Glad you got it sorted.