Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code returns TRUE instead of value
Essentially what I am trying to do is this:
I am trying to calculate depreciation. I use the DateAdd function to add x number of months(Cell "F & Row that is active") to the acquisition date(Cell "H & Row that is active" of an asset. If the sum of those dates is greater than the End of life (Cell "I & Row that is active") of that asset then there is a payment for that month and the value in Range("K" & Row) is place in the active cell until the entire cell range from M7 to AC63 is filled. If the sum of those dates is not greater the End of life (Cell "I & Row that is active") of that asset then a value of zero is place in that cell. In either case the value in (Cell "F & Row that is active") decremented by 1 for the next time the loop is run. It runs, and the range is being filled, but there is not supposed to be a payment for every cell in the range. There are no zeros for when the condition is false. Additionally, the entire Column "M" only has the word TRUE and not a numerical value. My macro is as follows. Please help. Sub Fill() Dim TestDate As Date Dim Col As Integer Dim Row As Long For Col = 13 To 29 For Row = 7 To 63 TestDate = DateAdd("m", Range("F" & Row), _ Range("H" & Row)) If TestDate < Range("I" & Row) Then Range("K" & Row).Copy Cells(Row, Col) = Range("K" & Row) Range("F" & Row).Value = Range("F" & Row).Value - 1 Else Cells(Row, Col) = ActiveCell.Value = 0 Range("F" & Row).Value = Range("F" & Row).Value - 1 End If Next Row Next Col End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code returns TRUE instead of value
Loren,
This line Cells(Row, Col) = ActiveCell.Value = 0 evaluates "ActiveCell.Value = 0", which will either True or False. It is this answer that you put in Cells(Row, Col).Value. From your description "If the sum of those dates is not greater the End of life (Cell "I & Row that is active") of that asset then a value of zero is place in that cell.", I would guess you mean: Cells(Row, Col).Value = 0 NickHK "loren.pottinger" groups.com... Essentially what I am trying to do is this: I am trying to calculate depreciation. I use the DateAdd function to add x number of months(Cell "F & Row that is active") to the acquisition date(Cell "H & Row that is active" of an asset. If the sum of those dates is greater than the End of life (Cell "I & Row that is active") of that asset then there is a payment for that month and the value in Range("K" & Row) is place in the active cell until the entire cell range from M7 to AC63 is filled. If the sum of those dates is not greater the End of life (Cell "I & Row that is active") of that asset then a value of zero is place in that cell. In either case the value in (Cell "F & Row that is active") decremented by 1 for the next time the loop is run. It runs, and the range is being filled, but there is not supposed to be a payment for every cell in the range. There are no zeros for when the condition is false. Additionally, the entire Column "M" only has the word TRUE and not a numerical value. My macro is as follows. Please help. Sub Fill() Dim TestDate As Date Dim Col As Integer Dim Row As Long For Col = 13 To 29 For Row = 7 To 63 TestDate = DateAdd("m", Range("F" & Row), _ Range("H" & Row)) If TestDate < Range("I" & Row) Then Range("K" & Row).Copy Cells(Row, Col) = Range("K" & Row) Range("F" & Row).Value = Range("F" & Row).Value - 1 Else Cells(Row, Col) = ActiveCell.Value = 0 Range("F" & Row).Value = Range("F" & Row).Value - 1 End If Next Row Next Col End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code returns TRUE instead of value
You have several replies to your several posts.
Please don't multipost. "loren.pottinger" wrote: Essentially what I am trying to do is this: I am trying to calculate depreciation. I use the DateAdd function to add x number of months(Cell "F & Row that is active") to the acquisition date(Cell "H & Row that is active" of an asset. If the sum of those dates is greater than the End of life (Cell "I & Row that is active") of that asset then there is a payment for that month and the value in Range("K" & Row) is place in the active cell until the entire cell range from M7 to AC63 is filled. If the sum of those dates is not greater the End of life (Cell "I & Row that is active") of that asset then a value of zero is place in that cell. In either case the value in (Cell "F & Row that is active") decremented by 1 for the next time the loop is run. It runs, and the range is being filled, but there is not supposed to be a payment for every cell in the range. There are no zeros for when the condition is false. Additionally, the entire Column "M" only has the word TRUE and not a numerical value. My macro is as follows. Please help. Sub Fill() Dim TestDate As Date Dim Col As Integer Dim Row As Long For Col = 13 To 29 For Row = 7 To 63 TestDate = DateAdd("m", Range("F" & Row), _ Range("H" & Row)) If TestDate < Range("I" & Row) Then Range("K" & Row).Copy Cells(Row, Col) = Range("K" & Row) Range("F" & Row).Value = Range("F" & Row).Value - 1 Else Cells(Row, Col) = ActiveCell.Value = 0 Range("F" & Row).Value = Range("F" & Row).Value - 1 End If Next Row Next Col End Sub -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code returns TRUE instead of value
It is not executing my If-Then-Else statement.
It seems the only statement being executed is: Cells(Row, Col).Value = Range("K" & Row) and I only want it to do that if the condition is true, and put a 0 in each cell in the range if it is false. Can someone please look at my code and help me to get it to execute the If-Then-Else statement. Thank you. Updated code as follows: Sub Fill() Dim TestDate As Date Dim Col As Integer Dim Row As Long For Col = 13 To 29 For Row = 7 To 63 With TestDate = DateAdd("m", Range("F" & Row).Value, _ Range("H" & Row).Value) If TestDate < Range("I" & Row).Value Then Cells(Row, Col).Value = Range("K" & Row) Range("F" & Row).Value = Range("F" & Row).Value - 1 Else Cells(Row, Col).Value = 0 Range("F" & Row).Value = Range("F" & Row).Value - 1 End If End With Next Row Next Col End Sub Dave Peterson wrote: You have several replies to your several posts. Please don't multipost. "loren.pottinger" wrote: Essentially what I am trying to do is this: I am trying to calculate depreciation. I use the DateAdd function to add x number of months(Cell "F & Row that is active") to the acquisition date(Cell "H & Row that is active" of an asset. If the sum of those dates is greater than the End of life (Cell "I & Row that is active") of that asset then there is a payment for that month and the value in Range("K" & Row) is place in the active cell until the entire cell range from M7 to AC63 is filled. If the sum of those dates is not greater the End of life (Cell "I & Row that is active") of that asset then a value of zero is place in that cell. In either case the value in (Cell "F & Row that is active") decremented by 1 for the next time the loop is run. It runs, and the range is being filled, but there is not supposed to be a payment for every cell in the range. There are no zeros for when the condition is false. Additionally, the entire Column "M" only has the word TRUE and not a numerical value. My macro is as follows. Please help. Sub Fill() Dim TestDate As Date Dim Col As Integer Dim Row As Long For Col = 13 To 29 For Row = 7 To 63 TestDate = DateAdd("m", Range("F" & Row), _ Range("H" & Row)) If TestDate < Range("I" & Row) Then Range("K" & Row).Copy Cells(Row, Col) = Range("K" & Row) Range("F" & Row).Value = Range("F" & Row).Value - 1 Else Cells(Row, Col) = ActiveCell.Value = 0 Range("F" & Row).Value = Range("F" & Row).Value - 1 End If Next Row Next Col End Sub -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code returns TRUE instead of value
Another multipost.
"loren.pottinger" wrote: It is not executing my If-Then-Else statement. It seems the only statement being executed is: Cells(Row, Col).Value = Range("K" & Row) and I only want it to do that if the condition is true, and put a 0 in each cell in the range if it is false. Can someone please look at my code and help me to get it to execute the If-Then-Else statement. Thank you. Updated code as follows: Sub Fill() Dim TestDate As Date Dim Col As Integer Dim Row As Long For Col = 13 To 29 For Row = 7 To 63 With TestDate = DateAdd("m", Range("F" & Row).Value, _ Range("H" & Row).Value) If TestDate < Range("I" & Row).Value Then Cells(Row, Col).Value = Range("K" & Row) Range("F" & Row).Value = Range("F" & Row).Value - 1 Else Cells(Row, Col).Value = 0 Range("F" & Row).Value = Range("F" & Row).Value - 1 End If End With Next Row Next Col End Sub Dave Peterson wrote: You have several replies to your several posts. Please don't multipost. "loren.pottinger" wrote: Essentially what I am trying to do is this: I am trying to calculate depreciation. I use the DateAdd function to add x number of months(Cell "F & Row that is active") to the acquisition date(Cell "H & Row that is active" of an asset. If the sum of those dates is greater than the End of life (Cell "I & Row that is active") of that asset then there is a payment for that month and the value in Range("K" & Row) is place in the active cell until the entire cell range from M7 to AC63 is filled. If the sum of those dates is not greater the End of life (Cell "I & Row that is active") of that asset then a value of zero is place in that cell. In either case the value in (Cell "F & Row that is active") decremented by 1 for the next time the loop is run. It runs, and the range is being filled, but there is not supposed to be a payment for every cell in the range. There are no zeros for when the condition is false. Additionally, the entire Column "M" only has the word TRUE and not a numerical value. My macro is as follows. Please help. Sub Fill() Dim TestDate As Date Dim Col As Integer Dim Row As Long For Col = 13 To 29 For Row = 7 To 63 TestDate = DateAdd("m", Range("F" & Row), _ Range("H" & Row)) If TestDate < Range("I" & Row) Then Range("K" & Row).Copy Cells(Row, Col) = Range("K" & Row) Range("F" & Row).Value = Range("F" & Row).Value - 1 Else Cells(Row, Col) = ActiveCell.Value = 0 Range("F" & Row).Value = Range("F" & Row).Value - 1 End If Next Row Next Col End Sub -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code returns TRUE instead of value
I waited like 15 mins before doing so............................It's
kinda crunch time around here. I apologize. Dave Peterson wrote: Another multipost. "loren.pottinger" wrote: It is not executing my If-Then-Else statement. It seems the only statement being executed is: Cells(Row, Col).Value = Range("K" & Row) and I only want it to do that if the condition is true, and put a 0 in each cell in the range if it is false. Can someone please look at my code and help me to get it to execute the If-Then-Else statement. Thank you. Updated code as follows: Sub Fill() Dim TestDate As Date Dim Col As Integer Dim Row As Long For Col = 13 To 29 For Row = 7 To 63 With TestDate = DateAdd("m", Range("F" & Row).Value, _ Range("H" & Row).Value) If TestDate < Range("I" & Row).Value Then Cells(Row, Col).Value = Range("K" & Row) Range("F" & Row).Value = Range("F" & Row).Value - 1 Else Cells(Row, Col).Value = 0 Range("F" & Row).Value = Range("F" & Row).Value - 1 End If End With Next Row Next Col End Sub Dave Peterson wrote: You have several replies to your several posts. Please don't multipost. "loren.pottinger" wrote: Essentially what I am trying to do is this: I am trying to calculate depreciation. I use the DateAdd function to add x number of months(Cell "F & Row that is active") to the acquisition date(Cell "H & Row that is active" of an asset. If the sum of those dates is greater than the End of life (Cell "I & Row that is active") of that asset then there is a payment for that month and the value in Range("K" & Row) is place in the active cell until the entire cell range from M7 to AC63 is filled. If the sum of those dates is not greater the End of life (Cell "I & Row that is active") of that asset then a value of zero is place in that cell. In either case the value in (Cell "F & Row that is active") decremented by 1 for the next time the loop is run. It runs, and the range is being filled, but there is not supposed to be a payment for every cell in the range. There are no zeros for when the condition is false. Additionally, the entire Column "M" only has the word TRUE and not a numerical value. My macro is as follows. Please help. Sub Fill() Dim TestDate As Date Dim Col As Integer Dim Row As Long For Col = 13 To 29 For Row = 7 To 63 TestDate = DateAdd("m", Range("F" & Row), _ Range("H" & Row)) If TestDate < Range("I" & Row) Then Range("K" & Row).Copy Cells(Row, Col) = Range("K" & Row) Range("F" & Row).Value = Range("F" & Row).Value - 1 Else Cells(Row, Col) = ActiveCell.Value = 0 Range("F" & Row).Value = Range("F" & Row).Value - 1 End If Next Row Next Col End Sub -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code returns TRUE instead of value
It may be crunch time for you, but you're wasting your time by having to check
multiple newsgroups. And you're wasting the time of responders if the answer was already posted. My post is more of a warning to others. They may not want to bother if you have a response elsewhere. "loren.pottinger" wrote: I waited like 15 mins before doing so............................It's kinda crunch time around here. I apologize. Dave Peterson wrote: Another multipost. "loren.pottinger" wrote: It is not executing my If-Then-Else statement. It seems the only statement being executed is: Cells(Row, Col).Value = Range("K" & Row) and I only want it to do that if the condition is true, and put a 0 in each cell in the range if it is false. Can someone please look at my code and help me to get it to execute the If-Then-Else statement. Thank you. Updated code as follows: Sub Fill() Dim TestDate As Date Dim Col As Integer Dim Row As Long For Col = 13 To 29 For Row = 7 To 63 With TestDate = DateAdd("m", Range("F" & Row).Value, _ Range("H" & Row).Value) If TestDate < Range("I" & Row).Value Then Cells(Row, Col).Value = Range("K" & Row) Range("F" & Row).Value = Range("F" & Row).Value - 1 Else Cells(Row, Col).Value = 0 Range("F" & Row).Value = Range("F" & Row).Value - 1 End If End With Next Row Next Col End Sub Dave Peterson wrote: You have several replies to your several posts. Please don't multipost. "loren.pottinger" wrote: Essentially what I am trying to do is this: I am trying to calculate depreciation. I use the DateAdd function to add x number of months(Cell "F & Row that is active") to the acquisition date(Cell "H & Row that is active" of an asset. If the sum of those dates is greater than the End of life (Cell "I & Row that is active") of that asset then there is a payment for that month and the value in Range("K" & Row) is place in the active cell until the entire cell range from M7 to AC63 is filled. If the sum of those dates is not greater the End of life (Cell "I & Row that is active") of that asset then a value of zero is place in that cell. In either case the value in (Cell "F & Row that is active") decremented by 1 for the next time the loop is run. It runs, and the range is being filled, but there is not supposed to be a payment for every cell in the range. There are no zeros for when the condition is false. Additionally, the entire Column "M" only has the word TRUE and not a numerical value. My macro is as follows. Please help. Sub Fill() Dim TestDate As Date Dim Col As Integer Dim Row As Long For Col = 13 To 29 For Row = 7 To 63 TestDate = DateAdd("m", Range("F" & Row), _ Range("H" & Row)) If TestDate < Range("I" & Row) Then Range("K" & Row).Copy Cells(Row, Col) = Range("K" & Row) Range("F" & Row).Value = Range("F" & Row).Value - 1 Else Cells(Row, Col) = ActiveCell.Value = 0 Range("F" & Row).Value = Range("F" & Row).Value - 1 End If Next Row Next Col End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
=IF(test,true,false) only ever returns "true"? | Excel Worksheet Functions | |||
Any number above 15 returns false when it should be true | Excel Worksheet Functions | |||
An if statment tat returns a true blank | Excel Worksheet Functions | |||
If Statement returns true when false? | Excel Discussion (Misc queries) | |||
Code returns TRUE instead of value | Excel Discussion (Misc queries) |