# If - Then Code Help needed

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

B

#### Bob Phillips

=IF(HOUR(G2)<4,formula1,formula2)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

S

#### Stefi

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:

S

#### Scott

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?

K

#### Kent Prokopy

If Range("G1").Value >= Date + TimeSerial(0, 0, 0) And Range("G1").Value <=
Date + TimeSerial(4, 0, 0) Then
DoEvents
Else
DoEvents
End If

S

#### 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

J

#### John_John

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

J

#### John_John

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. ;-)

S

#### 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

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

S

#### 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

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

J

#### John_John

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

S

#### Scott

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??