Type Mismatch error when subtracting two cells in VBA

[AC1982]

Hi,

I am trying to subtract and add a range of cells in VBA, but I keep getting a run-time error 13 Type mismatch error. Can anyone help, here is my code -

ActiveSheet.Range("G55").Value = Range("G56").Value - WorksheetFunction.Sum(Range("G56"), Range("G20"), Range("G31"), Range("G40"), Range("G49"), Range("G53:G54"))

I want the value of G55 to be G56-SUM(G20,G31,G40,G49,G53,G54), I am not sure how to fix this.

 

[mikerickson]

Try

Range("G55").Value = Val(Range("G56").Value - Application.SUM(Range("G20,G31,G40,G49,G53,G54)))

 

[AC1982]

Try

Range("G55").Value = Val(Range("G56").Value - Application.SUM(Range("G20,G31,G40,G49,G53,G54)))

Hi Mike,

Thanks for your help.

When I type the code below in, I still get the same error.

Range("G55").Value = Val(Range("G56").Value - _
Application.Sum(Range("G20, G31, G40, G49, G53, G54")))

 

[mikerickson]

Correct parenthetic placement would help both of us

Range("G55").Value = Val(Range("G56").Value) - Application.SUM(Range("G20,G31,G40,G49,G53,G54"))

 

[AC1982]

Thanks works great.

I am trying to divide two cells by eachother and I also get the same error (type mismatch) -

Range("H55") = Range("G55").Value / Range("G56").Value

I am not sure why this is happening and how I can avoid it in the future.

 

[Nalani]

Looks like Mike may be Offline, so

Following the same concept as your subtracting:

Range("H55") = Val(Range("G55").Value / Val(Range("G56"))

 

[mikerickson]

Yes, but to get the formula absolutley bullet proof, I'd use CStr to coerce any error value to a string and then Val to get a number

Range("H55") = Val(CStr(Range("G55").Value)) / Val(CStr(Range("G56")))

But that would error if G56 doesn't hold a non-zero number (div by 0)

It might be better to use Excel's built in error handling so that the VB routine doesn't crash

Range("H55").FormulaR1C1 = "=RC[-1]/R[1]C[-1]"