| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Okay, dumb question. | Excel Programming | |||
| Dumb question | Excel Programming | |||
| Dumb question of the day. | Excel Programming | |||
| Dumb Question | Excel Programming | |||
| Dumb Dim Question | Excel Programming | |||
Linear Mode
