If - Then Code Help needed

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

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(HOUR(G2)<4,formula1,formula2)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
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:
 
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?
 
If Range("G1").Value >= Date + TimeSerial(0, 0, 0) And Range("G1").Value <=
Date + TimeSerial(4, 0, 0) Then
DoEvents
Else
DoEvents
End If
 
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
 
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 said:
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
Click to expand...
Click to expand...
 
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. ;-)
 
John_John,

I don't see the correct version you are refering to??


John_John said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
John_John,

I don't see the correct version you are refering to??

John_John said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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


:

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
Click to expand...
Click to expand...
Click to expand...
 
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??
 
Back
Top