dumb question about subtraction
 

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!

dumb question about subtraction
 

Check out the declaration part..


Dim i As Long

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

--
Jacob


"Diana" wrote:

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!

dumb question about subtraction
 

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.


"Jacob Skaria" wrote:

Check out the declaration part..


Dim i As Long

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

--
Jacob


"Diana" wrote:

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!

dumb question about subtraction
 

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


--


Gary Keramidas
Excel 2003


"Diana" wrote in message
...
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!

dumb question about subtraction
 

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

--
Jacob


"Diana" wrote:

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.


"Jacob Skaria" wrote:

Check out the declaration part..


Dim i As Long

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

--
Jacob


"Diana" wrote:

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!

dumb question about subtraction
 

Thanks Jacob!

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

"Jacob Skaria" wrote:

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

--
Jacob


"Diana" wrote:

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.


"Jacob Skaria" wrote:

Check out the declaration part..


Dim i As Long

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

--
Jacob


"Diana" wrote:

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!

dumb question about subtraction
 

It avoids trying to do math on text strings.

--

HTH

Bob

"Diana" wrote in message
...
Thanks Jacob!

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

"Jacob Skaria" wrote:

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

--
Jacob


"Diana" wrote:

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.


"Jacob Skaria" wrote:

Check out the declaration part..


Dim i As Long

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

--
Jacob


"Diana" wrote:

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!