Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
If-Then-Else Statement
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
If-Then-Else Statement
Loren says that responses should go to the .misc post.
"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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
If-Then-Else Statement
I'm not sure what's in those cells (I'd look there next), but the with statement
doesn't make much sense to me. Option Explicit Sub myFill() Dim TestDate As Date Dim myCol As Long Dim myRow As Long For myCol = 13 To 29 For myRow = 7 To 63 TestDate = DateAdd("m", Range("F" & myRow).Value, _ Range("H" & myRow).Value) If TestDate < Range("I" & myRow).Value Then Cells(myRow, myCol).Value = Range("K" & myRow) Range("F" & myRow).Value = Range("F" & myRow).Value - 1 Else Cells(myRow, myCol).Value = 0 Range("F" & myRow).Value = Range("F" & myRow).Value - 1 End If End With Next myRow Next myCol End Sub And I'd stay away from using Row, Fill as variable/sub names. They are reserved words in excel. "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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
If-Then-Else Statement
I deleted the With statment, but missed the "end with" statement. Remove that,
too. Dave Peterson wrote: I'm not sure what's in those cells (I'd look there next), but the with statement doesn't make much sense to me. Option Explicit Sub myFill() Dim TestDate As Date Dim myCol As Long Dim myRow As Long For myCol = 13 To 29 For myRow = 7 To 63 TestDate = DateAdd("m", Range("F" & myRow).Value, _ Range("H" & myRow).Value) If TestDate < Range("I" & myRow).Value Then Cells(myRow, myCol).Value = Range("K" & myRow) Range("F" & myRow).Value = Range("F" & myRow).Value - 1 Else Cells(myRow, myCol).Value = 0 Range("F" & myRow).Value = Range("F" & myRow).Value - 1 End If End With Next myRow Next myCol End Sub And I'd stay away from using Row, Fill as variable/sub names. They are reserved words in excel. "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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
If-Then-Else Statement
Thanks Dave. It is still not reading my If-Then-Else. There are no 0s
in the cells where the condition is false. The information in the for the DateAdd function is correct though. I'm certain that is not the problem. Dave Peterson wrote: I deleted the With statment, but missed the "end with" statement. Remove that, too. Dave Peterson wrote: I'm not sure what's in those cells (I'd look there next), but the with statement doesn't make much sense to me. Option Explicit Sub myFill() Dim TestDate As Date Dim myCol As Long Dim myRow As Long For myCol = 13 To 29 For myRow = 7 To 63 TestDate = DateAdd("m", Range("F" & myRow).Value, _ Range("H" & myRow).Value) If TestDate < Range("I" & myRow).Value Then Cells(myRow, myCol).Value = Range("K" & myRow) Range("F" & myRow).Value = Range("F" & myRow).Value - 1 Else Cells(myRow, myCol).Value = 0 Range("F" & myRow).Value = Range("F" & myRow).Value - 1 End If End With Next myRow Next myCol End Sub And I'd stay away from using Row, Fill as variable/sub names. They are reserved words in excel. "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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
If-Then-Else Statement
I'd add some debug.print statements to verify what you think is true:
debug.print testdate & vblf & Range("I" & myRow).Value for example. "loren.pottinger" wrote: Thanks Dave. It is still not reading my If-Then-Else. There are no 0s in the cells where the condition is false. The information in the for the DateAdd function is correct though. I'm certain that is not the problem. Dave Peterson wrote: I deleted the With statment, but missed the "end with" statement. Remove that, too. Dave Peterson wrote: I'm not sure what's in those cells (I'd look there next), but the with statement doesn't make much sense to me. Option Explicit Sub myFill() Dim TestDate As Date Dim myCol As Long Dim myRow As Long For myCol = 13 To 29 For myRow = 7 To 63 TestDate = DateAdd("m", Range("F" & myRow).Value, _ Range("H" & myRow).Value) If TestDate < Range("I" & myRow).Value Then Cells(myRow, myCol).Value = Range("K" & myRow) Range("F" & myRow).Value = Range("F" & myRow).Value - 1 Else Cells(myRow, myCol).Value = 0 Range("F" & myRow).Value = Range("F" & myRow).Value - 1 End If End With Next myRow Next myCol End Sub And I'd stay away from using Row, Fill as variable/sub names. They are reserved words in excel. "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 -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
If-Then-Else Statement
Thanks dave, but where would I put that exactly? Would I put it under
this statement: If TestDate < Range("I" & myRow).Value Then If that's the case, I did and nothing happened, so does that mean it's not doing what I want it to do? And do you have any thoughts on how I may go about fixing it? Dave Peterson wrote: I'd add some debug.print statements to verify what you think is true: debug.print testdate & vblf & Range("I" & myRow).Value for example. "loren.pottinger" wrote: Thanks Dave. It is still not reading my If-Then-Else. There are no 0s in the cells where the condition is false. The information in the for the DateAdd function is correct though. I'm certain that is not the problem. Dave Peterson wrote: I deleted the With statment, but missed the "end with" statement. Remove that, too. Dave Peterson wrote: I'm not sure what's in those cells (I'd look there next), but the with statement doesn't make much sense to me. Option Explicit Sub myFill() Dim TestDate As Date Dim myCol As Long Dim myRow As Long For myCol = 13 To 29 For myRow = 7 To 63 TestDate = DateAdd("m", Range("F" & myRow).Value, _ Range("H" & myRow).Value) If TestDate < Range("I" & myRow).Value Then Cells(myRow, myCol).Value = Range("K" & myRow) Range("F" & myRow).Value = Range("F" & myRow).Value - 1 Else Cells(myRow, myCol).Value = 0 Range("F" & myRow).Value = Range("F" & myRow).Value - 1 End If End With Next myRow Next myCol End Sub And I'd stay away from using Row, Fill as variable/sub names. They are reserved words in excel. "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 -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
If-Then-Else Statement
Put it before that line.
Then you'll know what is in those variables/cells before that line gets executed. "loren.pottinger" wrote: Thanks dave, but where would I put that exactly? Would I put it under this statement: If TestDate < Range("I" & myRow).Value Then If that's the case, I did and nothing happened, so does that mean it's not doing what I want it to do? And do you have any thoughts on how I may go about fixing it? Dave Peterson wrote: I'd add some debug.print statements to verify what you think is true: debug.print testdate & vblf & Range("I" & myRow).Value for example. "loren.pottinger" wrote: Thanks Dave. It is still not reading my If-Then-Else. There are no 0s in the cells where the condition is false. The information in the for the DateAdd function is correct though. I'm certain that is not the problem. Dave Peterson wrote: I deleted the With statment, but missed the "end with" statement. Remove that, too. Dave Peterson wrote: I'm not sure what's in those cells (I'd look there next), but the with statement doesn't make much sense to me. Option Explicit Sub myFill() Dim TestDate As Date Dim myCol As Long Dim myRow As Long For myCol = 13 To 29 For myRow = 7 To 63 TestDate = DateAdd("m", Range("F" & myRow).Value, _ Range("H" & myRow).Value) If TestDate < Range("I" & myRow).Value Then Cells(myRow, myCol).Value = Range("K" & myRow) Range("F" & myRow).Value = Range("F" & myRow).Value - 1 Else Cells(myRow, myCol).Value = 0 Range("F" & myRow).Value = Range("F" & myRow).Value - 1 End If End With Next myRow Next myCol End Sub And I'd stay away from using Row, Fill as variable/sub names. They are reserved words in excel. "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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protect IF statement AND allow data entry | Excel Worksheet Functions | |||
SQL concatenation statement | Excel Discussion (Misc queries) | |||
SET statement tutorial | Excel Discussion (Misc queries) | |||
If statement | Excel Discussion (Misc queries) | |||
Do I need a sumif or sum of a vlookup formula? | Excel Worksheet Functions |