dumb question about subtraction

[Diana]

I want to put in Column M the result of the number in Column L - that in
Column J - that in Column K.

If I was doing this in a worksheet, and using row 9 as an example, I would
type in M9 the formula =L9-J9-K9

For the life of me I cannot get this to work in VBA. I have tried various
variations of the following, none works.

Cells(i, "M") = Cells(i, "L") - Cells(i, "J") - Cells(i, "K")

I get "Type mismatch" error.

All I want to do is a simple subtraction!

 

[Jacob Skaria]

Check out the declaration part..


Dim i As Long

i = 9
Cells(i, "M") = Cells(i, "L") - Cells(i, "J") - Cells(i, "K")

 

[Diana]

Hi Jacob


i has been defined as Long.

The statement is part of a loop:

For i = 2000 To 2 Step -1
If InStr(1, Cells(i, "I"), "Total", vbTextCompare) Then
Cells(i, "M") = Cells(i, "L") - Cells(i, "J") - Cells(i, "K")
End If
Next

The i tells me the row. I only want to do the subtraction where there is a
total.

 

[Gary Keramidas]

you code worked for me. i also changed it to a formula so you see the the
formula in the cell instead of the result.

Sub test()
Dim i As Long

For i = 2000 To 2 Step -1
If InStr(1, Cells(i, "I"), "Total", vbTextCompare) Then
Cells(i, "M").Formula = "=" & Cells(i, "L").Address & "-" &
Cells(i, _
"J").Address & "- " & Cells(i, "K").Address
End If
Next

End Sub

 

[Jacob Skaria]

Check out for any non-numeric values in those cells...

For i = 2000 To 2 Step -1
If InStr(1, Cells(i, "I"), "Total", vbTextCompare) Then
If IsNumeric(Cells(i, "L")) And IsNumeric(Cells(i, "J")) And
IsNumeric(Cells(i, "K")) Then
Cells(i, "M") = Cells(i, "L") - Cells(i, "J") - Cells(i, "K")
End If
End If
Next

 

[Diana]

Thanks Jacob!

It seems the mere act of checking solves the problem, dare I ask why?

 

[Bob Phillips]

It avoids trying to do math on text strings.