|
If - Then Code Help needed
I am creating a new column for an Excel program but this column needs to use
two different formulas based on a Start time in column G. If the Start time is before midnight then I use one fomula and if the start time is after midnight but before 4 a.m. then I use a different formula. The formulas work perfect but I can't seem to write the initial condition correctly so that it knows which formula to use. Essentially, how could I write an If Then statement that says if the scan time in cell ( #, G ) is 24:00 and < 04:00 then Calculation # 1 Else Calculation # 2 |
If - Then Code Help needed
=IF(HOUR(G2)<4,formula1,formula2)
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Scott" wrote in message ... I am creating a new column for an Excel program but this column needs to use two different formulas based on a Start time in column G. If the Start time is before midnight then I use one fomula and if the start time is after midnight but before 4 a.m. then I use a different formula. The formulas work perfect but I can't seem to write the initial condition correctly so that it knows which formula to use. Essentially, how could I write an If Then statement that says if the scan time in cell ( #, G ) is 24:00 and < 04:00 then Calculation # 1 Else Calculation # 2 |
If - Then Code Help needed
If Range("G1").Value TimeSerial(0, 0, 0) _
And Range("G1").Value < TimeSerial(4, 0, 0) Then ' Calculation # 1 Else ' Calculation # 2 End If Regards, Stefi Scott ezt *rta: I am creating a new column for an Excel program but this column needs to use two different formulas based on a Start time in column G. If the Start time is before midnight then I use one fomula and if the start time is after midnight but before 4 a.m. then I use a different formula. The formulas work perfect but I can't seem to write the initial condition correctly so that it knows which formula to use. Essentially, how could I write an If Then statement that says if the scan time in cell ( #, G ) is 24:00 and < 04:00 then Calculation # 1 Else Calculation # 2 |
If - Then Code Help needed
Both of these make sense but even trying both gives me errors. I think the
problem lies with the formulas I am using that already contain some nested If Then statements. Let me post them and see if anyone knows how to integrate this. Here is the formula I am using if it is 24:00 and <04:00: ActiveCell.FormulaR1C1 = _ "=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-R[-1]C[-28],1)*1440,0),0)" Here is the formula I am using for the Rest: ActiveCell.FormulaR1C1 = _ "=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-MAX(R[-1]C[-28],(VLOOKUP(RC[-35],RRInfo,5,FALSE))),1)*1440,0),0)" How can I get those into the If Then statements recommended? "Stefi" wrote: If Range("G1").Value TimeSerial(0, 0, 0) _ And Range("G1").Value < TimeSerial(4, 0, 0) Then ' Calculation # 1 Else ' Calculation # 2 End If Regards, Stefi Scott ezt *rta: I am creating a new column for an Excel program but this column needs to use two different formulas based on a Start time in column G. If the Start time is before midnight then I use one fomula and if the start time is after midnight but before 4 a.m. then I use a different formula. The formulas work perfect but I can't seem to write the initial condition correctly so that it knows which formula to use. Essentially, how could I write an If Then statement that says if the scan time in cell ( #, G ) is 24:00 and < 04:00 then Calculation # 1 Else Calculation # 2 |
If - Then Code Help needed
If Range("G1").Value = Date + TimeSerial(0, 0, 0) And Range("G1").Value <=
Date + TimeSerial(4, 0, 0) Then DoEvents Else DoEvents End If "Scott" wrote: I am creating a new column for an Excel program but this column needs to use two different formulas based on a Start time in column G. If the Start time is before midnight then I use one fomula and if the start time is after midnight but before 4 a.m. then I use a different formula. The formulas work perfect but I can't seem to write the initial condition correctly so that it knows which formula to use. Essentially, how could I write an If Then statement that says if the scan time in cell ( #, G ) is 24:00 and < 04:00 then Calculation # 1 Else Calculation # 2 |
If - Then Code Help needed
Here is all the code I am using in case anyone can help put this in the order
it needs to be. I have tried different configurations but always get an error of some sort. It also needs to keep moving down row by row and checking the start time in column G. There is a date as well in column F if that helps. It has gotten so complex that I can't wrap my brain around it anymore. Range("AJ2").Select If ("G1").Value TimeSerial(0, 0, 0) _ And Range("G1").Value < TimeSerial(4, 0, 0) Then ActiveCell.FormulaR1C1 = _ "=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-R[-1]C[-28],1)_ *1440,0),0)" Else ActiveCell.FormulaR1C1 = _ "=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-MAX(R[-1]C[-28], _ (VLOOKUP(RC[-35],RRInfo,5,FALSE))),1)*1440,0),0)" End If Selection.Copy Range("AJ2:AJ" & Lrow).Select ActiveSheet.Paste "Scott" wrote: I am creating a new column for an Excel program but this column needs to use two different formulas based on a Start time in column G. If the Start time is before midnight then I use one fomula and if the start time is after midnight but before 4 a.m. then I use a different formula. The formulas work perfect but I can't seem to write the initial condition correctly so that it knows which formula to use. Essentially, how could I write an If Then statement that says if the scan time in cell ( #, G ) is 24:00 and < 04:00 then Calculation # 1 Else Calculation # 2 |
If - Then Code Help needed
Your code contains some syntax errors.
See the correct version above : Range("AJ2").Select If Range("G1").Value TimeSerial(0, 0, 0) _ And Range("G1").Value < TimeSerial(4, 0, 0) Then ActiveCell.FormulaR1C1 = _ "=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-R[-1]C[-28],1)" _ & "*1440,0),0)" Else ActiveCell.FormulaR1C1 = _ "=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-MAX(R[-1]C[-28]," _ & "(VLOOKUP(RC[-35],RRInfo,5,FALSE))),1)*1440,0),0)" End If Selection.Copy Range("AJ2:AJ" & Lrow).Select ActiveSheet.Paste John Ο χρήστης "Scott" *γγραψε: Here is all the code I am using in case anyone can help put this in the order it needs to be. I have tried different configurations but always get an error of some sort. It also needs to keep moving down row by row and checking the start time in column G. There is a date as well in column F if that helps. It has gotten so complex that I can't wrap my brain around it anymore. Range("AJ2").Select If ("G1").Value TimeSerial(0, 0, 0) _ And Range("G1").Value < TimeSerial(4, 0, 0) Then ActiveCell.FormulaR1C1 = _ "=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-R[-1]C[-28],1)_ *1440,0),0)" Else ActiveCell.FormulaR1C1 = _ "=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-MAX(R[-1]C[-28], _ (VLOOKUP(RC[-35],RRInfo,5,FALSE))),1)*1440,0),0)" End If Selection.Copy Range("AJ2:AJ" & Lrow).Select ActiveSheet.Paste "Scott" wrote: I am creating a new column for an Excel program but this column needs to use two different formulas based on a Start time in column G. If the Start time is before midnight then I use one fomula and if the start time is after midnight but before 4 a.m. then I use a different formula. The formulas work perfect but I can't seem to write the initial condition correctly so that it knows which formula to use. Essentially, how could I write an If Then statement that says if the scan time in cell ( #, G ) is 24:00 and < 04:00 then Calculation # 1 Else Calculation # 2 |
If - Then Code Help needed
Remember also that any time value smaller than 4:00
is larger than 24:00. So the statment "Range("G1").Value TimeSerial(0, 0, 0)" is superfluous. ;-) |
If - Then Code Help needed
John_John,
I don't see the correct version you are refering to?? "John_John" wrote: Your code contains some syntax errors. See the correct version above : Range("AJ2").Select If Range("G1").Value TimeSerial(0, 0, 0) _ And Range("G1").Value < TimeSerial(4, 0, 0) Then ActiveCell.FormulaR1C1 = _ "=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-R[-1]C[-28],1)" _ & "*1440,0),0)" Else ActiveCell.FormulaR1C1 = _ "=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-MAX(R[-1]C[-28]," _ & "(VLOOKUP(RC[-35],RRInfo,5,FALSE))),1)*1440,0),0)" End If Selection.Copy Range("AJ2:AJ" & Lrow).Select ActiveSheet.Paste John Ο χρήστης "Scott" *γγραψε: Here is all the code I am using in case anyone can help put this in the order it needs to be. I have tried different configurations but always get an error of some sort. It also needs to keep moving down row by row and checking the start time in column G. There is a date as well in column F if that helps. It has gotten so complex that I can't wrap my brain around it anymore. Range("AJ2").Select If ("G1").Value TimeSerial(0, 0, 0) _ And Range("G1").Value < TimeSerial(4, 0, 0) Then ActiveCell.FormulaR1C1 = _ "=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-R[-1]C[-28],1)_ *1440,0),0)" Else ActiveCell.FormulaR1C1 = _ "=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-MAX(R[-1]C[-28], _ (VLOOKUP(RC[-35],RRInfo,5,FALSE))),1)*1440,0),0)" End If Selection.Copy Range("AJ2:AJ" & Lrow).Select ActiveSheet.Paste "Scott" wrote: I am creating a new column for an Excel program but this column needs to use two different formulas based on a Start time in column G. If the Start time is before midnight then I use one fomula and if the start time is after midnight but before 4 a.m. then I use a different formula. The formulas work perfect but I can't seem to write the initial condition correctly so that it knows which formula to use. Essentially, how could I write an If Then statement that says if the scan time in cell ( #, G ) is 24:00 and < 04:00 then Calculation # 1 Else Calculation # 2 |
If - Then Code Help needed
John_John,
I don't see the correct version you are refering to?? "John_John" wrote: Your code contains some syntax errors. See the correct version above : Range("AJ2").Select If Range("G1").Value TimeSerial(0, 0, 0) _ And Range("G1").Value < TimeSerial(4, 0, 0) Then ActiveCell.FormulaR1C1 = _ "=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-R[-1]C[-28],1)" _ & "*1440,0),0)" Else ActiveCell.FormulaR1C1 = _ "=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-MAX(R[-1]C[-28]," _ & "(VLOOKUP(RC[-35],RRInfo,5,FALSE))),1)*1440,0),0)" End If Selection.Copy Range("AJ2:AJ" & Lrow).Select ActiveSheet.Paste John Ο χρήστης "Scott" *γγραψε: Here is all the code I am using in case anyone can help put this in the order it needs to be. I have tried different configurations but always get an error of some sort. It also needs to keep moving down row by row and checking the start time in column G. There is a date as well in column F if that helps. It has gotten so complex that I can't wrap my brain around it anymore. Range("AJ2").Select If ("G1").Value TimeSerial(0, 0, 0) _ And Range("G1").Value < TimeSerial(4, 0, 0) Then ActiveCell.FormulaR1C1 = _ "=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-R[-1]C[-28],1)_ *1440,0),0)" Else ActiveCell.FormulaR1C1 = _ "=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-MAX(R[-1]C[-28], _ (VLOOKUP(RC[-35],RRInfo,5,FALSE))),1)*1440,0),0)" End If Selection.Copy Range("AJ2:AJ" & Lrow).Select ActiveSheet.Paste "Scott" wrote: I am creating a new column for an Excel program but this column needs to use two different formulas based on a Start time in column G. If the Start time is before midnight then I use one fomula and if the start time is after midnight but before 4 a.m. then I use a different formula. The formulas work perfect but I can't seem to write the initial condition correctly so that it knows which formula to use. Essentially, how could I write an If Then statement that says if the scan time in cell ( #, G ) is 24:00 and < 04:00 then Calculation # 1 Else Calculation # 2 |
If - Then Code Help needed
Range("AJ2").Select If Range("G1").Value TimeSerial(0, 0, 0) _ And Range("G1").Value < TimeSerial(4, 0, 0) Then ActiveCell.FormulaR1C1 = _ "=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-R[-1]C[-28],1)" _ & "*1440,0),0)" Else ActiveCell.FormulaR1C1 = _ "=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-MAX(R[-1]C[-28]," _ & "(VLOOKUP(RC[-35],RRInfo,5,FALSE))),1)*1440,0),0)" End If Selection.Copy Range("AJ2:AJ" & Lrow).Select ActiveSheet.Paste I am sorry Scott! My English are terrible. :-) Please let me know if you still occurs errors John Ο χρήστης "Scott" *γγραψε: John_John, I don't see the correct version you are refering to?? "John_John" wrote: Your code contains some syntax errors. See the correct version above : Range("AJ2").Select If Range("G1").Value TimeSerial(0, 0, 0) _ And Range("G1").Value < TimeSerial(4, 0, 0) Then ActiveCell.FormulaR1C1 = _ "=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-R[-1]C[-28],1)" _ & "*1440,0),0)" Else ActiveCell.FormulaR1C1 = _ "=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-MAX(R[-1]C[-28]," _ & "(VLOOKUP(RC[-35],RRInfo,5,FALSE))),1)*1440,0),0)" End If Selection.Copy Range("AJ2:AJ" & Lrow).Select ActiveSheet.Paste John Ο χρήστης "Scott" *γγραψε: Here is all the code I am using in case anyone can help put this in the order it needs to be. I have tried different configurations but always get an error of some sort. It also needs to keep moving down row by row and checking the start time in column G. There is a date as well in column F if that helps. It has gotten so complex that I can't wrap my brain around it anymore. Range("AJ2").Select If ("G1").Value TimeSerial(0, 0, 0) _ And Range("G1").Value < TimeSerial(4, 0, 0) Then ActiveCell.FormulaR1C1 = _ "=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-R[-1]C[-28],1)_ *1440,0),0)" Else ActiveCell.FormulaR1C1 = _ "=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-MAX(R[-1]C[-28], _ (VLOOKUP(RC[-35],RRInfo,5,FALSE))),1)*1440,0),0)" End If Selection.Copy Range("AJ2:AJ" & Lrow).Select ActiveSheet.Paste "Scott" wrote: I am creating a new column for an Excel program but this column needs to use two different formulas based on a Start time in column G. If the Start time is before midnight then I use one fomula and if the start time is after midnight but before 4 a.m. then I use a different formula. The formulas work perfect but I can't seem to write the initial condition correctly so that it knows which formula to use. Essentially, how could I write an If Then statement that says if the scan time in cell ( #, G ) is 24:00 and < 04:00 then Calculation # 1 Else Calculation # 2 |
If - Then Code Help needed
Bob,
I think this is the right track I need to take but I have found the formula is even more complex and can not get it to work. Hopefully you can help. It is going to have to be a nested If Then Statement. The first thing I need to do is see If B2=B1 If it is false then put 0. If it is true then need to test If Hour(G2)<4 If this is True then need this formula ROUND(MOD(G2-H1,1)*1440,0) else use this formula ROUND(MOD(G2-MAX(H1,(VLOOKUP(A2,RRInfo,5,FALSE))),1)*1440,0) Do you know how to put this together into one big formula?? "Bob Phillips" wrote: =IF(HOUR(G2)<4,formula1,formula2) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Scott" wrote in message ... I am creating a new column for an Excel program but this column needs to use two different formulas based on a Start time in column G. If the Start time is before midnight then I use one fomula and if the start time is after midnight but before 4 a.m. then I use a different formula. The formulas work perfect but I can't seem to write the initial condition correctly so that it knows which formula to use. Essentially, how could I write an If Then statement that says if the scan time in cell ( #, G ) is 24:00 and < 04:00 then Calculation # 1 Else Calculation # 2 |
| All times are GMT +1. The time now is 02:50 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com