Presented below is a table, a formula, and a macro incongruent with each
other. As a comparison, I have provided a table with cell values I would
like as a result of the formula/macro with cell values resulting from my
imperfect code.

Problem: RangeID2 should match RangeID values. RangeID values are
correct, whereas RangeID2 are not. I have attached a formula and code
below to demonstrate what I am trying to achieve. The code is in a
module in the file. This table is a representation of a large table I am
working with, and each line of the data table depicts a condition that I
am trying to meet. For some reason, the logic in the macro is
incongruent or does not match with RangeID, and the formula/code driving
RangeID2 values needs adjustment.

Dates such as 20030905 are formatted as general and not in Excel's date
format.

I have spent a long time with the formula/macro and cannot find a
solution. Need help, seriously!!

My table has the following data:
Range A1:L13
ORIGIN DEPTIM DESTIN ARRTIM AIR FLNO FRDAT TODAT ACTIVE ACFT RANGEID RAN
GEID2
DFW 1740 IAH 1848 DL 3791 20030901 20030930 YYYYYYY CRJ 1 0
DFW 735 IAH 905 8W 820 20030901 20030930 YYYYNNN D8Y 0 0
PHX 2030 IAH 51 HP 279 20030903 20030921 YYYYYNY 320 1 0
PHX 1201 IAH 1640 HP 273 20030922 20030930 YYYYYNN 733 0 1
DTW 1151 IAH 1349 NW 7889 20030904 20030930 YYYYYNY 735 1 0
DTW 1800 IAH 1959 CO 1789 20030905 20030905 NNNNYNN 738 0 0
MKE 815 IAH 1104 CO 2843 20030901 20030907 YYNYYNY ERJ 1 0
MKE 815 IAH 1104 CO 2843 20030908 20030929 YNNYYNY ERJ 0 1
PIT 1145 IAH 1340 US 334 20030901 20030906 YYYYYYN 733 1 0
PIT 1415 IAH 1631 CO 2429 20030908 20030914 YYYNYNY ERJ 0 1
IAH 742 DTW 1129 NW 6688 20030904 20030929 YNNYYNN 733 1 0
IAH 900 DTW 1240 CO 6832 20030902 20030927 YYYYYYN 319 0 0


The Cells in column L (column 12):
--------------------------------------------------------
=noparadise(ROW())
--------------------------------------------------------

The code that needs to be adjusted:
--------------------------------------------------------
Function noparadise(rowNum As Long) As Integer
If (Cells(rowNum, 7).Value <= 20030905) And _
(Cells(rowNum, 8).Value = 20030905) Or _
(UCase(Mid(Cells(rowNum, 9).Value, 5, 1)) < "Y") Then
noparadise = 0
Exit Function
End If
Select Case UCase(Cells(rowNum, 5).Value)
Case "NW"
Select Case UCase(Cells(rowNum, 1))
Case "DTW", "MEM", "MSP" 'condition 1
noparadise = 1
Case Else
Select Case UCase(Cells(rowNum, 3))
Case "DTW", "MEM", "MSP" 'condition 1
noparadise = 1
Case Else 'condition 2
noparadise = 0
End Select
End Select
Case "CO"
Select Case UCase(Cells(rowNum, 1))
Case "DTW", "MEM", "MSP" 'condition 3
noparadise = 0
Case Else
Select Case UCase(Cells(rowNum, 3))
Case "DTW", "MEM", "MSP" 'condition 3
noparadise = 0
Case Else 'condition 4
noparadise = 1
End Select
End Select
Case Else 'condition 5
noparadise = 1
End Select
End Function

Sub paradise()
For x = 2 To 30001
Cells(x, 12).Value = noparadise(x)
Next
End Sub
---------------------------------------------------------


A formula that does work in column K is:
---------------------------------------------------------
=IF(AND(IF(20030905=G2,IF(20030905<=H2,IF(MID(I2, 5,1)="Y",1,0),0),0)=1,
IF(OR(A2="DTW",A2="MEM",A2="MSP",C2="DTW",C2="MEM" ,C2="MSP"),IF(E2="CO",
0,1),IF(E2="NW",0,1))=1),1,0)

---------------------------------------------------------
If you wonder why I am asking for assistance in adjusting code when I
have a formula that works is because the macro code will substitute the
use of this long formula in column K, and for clear discrete operation
behind the scenes.

All assistance is greatly appreciated.

Thanks.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

I believe the first part of your code should be:

If Cells(rowNum, 7).Value 20030905 Or _
Cells(rowNum, 8).Value < 20030905 Or _
UCase(Mid(Cells(rowNum, 9).Value, 5, 1)) < "Y" Then
noparadise = 0
Exit Function
End If

--
HTH,
Dianne

In ,
coventry england typed:
Presented below is a table, a formula, and a macro incongruent with
each other. As a comparison, I have provided a table with cell values
I would like as a result of the formula/macro with cell values
resulting from my imperfect code.

Problem: RangeID2 should match RangeID values. RangeID values are
correct, whereas RangeID2 are not. I have attached a formula and code
below to demonstrate what I am trying to achieve. The code is in a
module in the file. This table is a representation of a large table I
am working with, and each line of the data table depicts a condition
that I am trying to meet. For some reason, the logic in the macro is
incongruent or does not match with RangeID, and the formula/code
driving RangeID2 values needs adjustment.

Dates such as 20030905 are formatted as general and not in Excel's
date format.

I have spent a long time with the formula/macro and cannot find a
solution. Need help, seriously!!

My table has the following data:
Range A1:L13
ORIGIN DEPTIM DESTIN ARRTIM AIR FLNO FRDAT TODAT ACTIVE ACFT RANGEID
RAN GEID2
DFW 1740 IAH 1848 DL 3791 20030901 20030930 YYYYYYY CRJ 1 0
DFW 735 IAH 905 8W 820 20030901 20030930 YYYYNNN D8Y 0 0
PHX 2030 IAH 51 HP 279 20030903 20030921 YYYYYNY 320 1 0
PHX 1201 IAH 1640 HP 273 20030922 20030930 YYYYYNN 733 0 1
DTW 1151 IAH 1349 NW 7889 20030904 20030930 YYYYYNY 735 1 0
DTW 1800 IAH 1959 CO 1789 20030905 20030905 NNNNYNN 738 0 0
MKE 815 IAH 1104 CO 2843 20030901 20030907 YYNYYNY ERJ 1 0
MKE 815 IAH 1104 CO 2843 20030908 20030929 YNNYYNY ERJ 0 1
PIT 1145 IAH 1340 US 334 20030901 20030906 YYYYYYN 733 1 0
PIT 1415 IAH 1631 CO 2429 20030908 20030914 YYYNYNY ERJ 0 1
IAH 742 DTW 1129 NW 6688 20030904 20030929 YNNYYNN 733 1 0
IAH 900 DTW 1240 CO 6832 20030902 20030927 YYYYYYN 319 0 0


The Cells in column L (column 12):
--------------------------------------------------------
=noparadise(ROW())
--------------------------------------------------------

The code that needs to be adjusted:
--------------------------------------------------------
Function noparadise(rowNum As Long) As Integer
If (Cells(rowNum, 7).Value <= 20030905) And _
(Cells(rowNum, 8).Value = 20030905) Or _
(UCase(Mid(Cells(rowNum, 9).Value, 5, 1)) < "Y") Then
noparadise = 0
Exit Function
End If
Select Case UCase(Cells(rowNum, 5).Value)
Case "NW"
Select Case UCase(Cells(rowNum, 1))
Case "DTW", "MEM", "MSP" 'condition 1
noparadise = 1
Case Else
Select Case UCase(Cells(rowNum, 3))
Case "DTW", "MEM", "MSP" 'condition 1
noparadise = 1
Case Else 'condition 2
noparadise = 0
End Select
End Select
Case "CO"
Select Case UCase(Cells(rowNum, 1))
Case "DTW", "MEM", "MSP" 'condition 3
noparadise = 0
Case Else
Select Case UCase(Cells(rowNum, 3))
Case "DTW", "MEM", "MSP" 'condition 3
noparadise = 0
Case Else 'condition 4
noparadise = 1
End Select
End Select
Case Else 'condition 5
noparadise = 1
End Select
End Function

Sub paradise()
For x = 2 To 30001
Cells(x, 12).Value = noparadise(x)
Next
End Sub
---------------------------------------------------------


A formula that does work in column K is:
---------------------------------------------------------

=IF(AND(IF(20030905=G2,IF(20030905<=H2,IF(MID(I2, 5,1)="Y",1,0),0),0)=1,

IF(OR(A2="DTW",A2="MEM",A2="MSP",C2="DTW",C2="MEM" ,C2="MSP"),IF(E2="CO",
0,1),IF(E2="NW",0,1))=1),1,0)

---------------------------------------------------------
If you wonder why I am asking for assistance in adjusting code when I
have a formula that works is because the macro code will substitute
the use of this long formula in column K, and for clear discrete
operation behind the scenes.

All assistance is greatly appreciated.

Thanks.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

It worked: you are a genius, cant believe the fix was that
easy....appreciate it very much.

Thanks.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!