Action to take when text in column is found

B

bigroo

Board Regular
Joined
Jun 22, 2008
Messages
74
Hello,</SPAN>

I am looking for some help here.</SPAN>
At present the aircrafts Not Built from their production lists are being missed by the code I have shown below. Below I have outlined what I want to do.</SPAN>
I have posted all the code to show you want I am doing, I only want to add a portion for column H “Operator Column”.</SPAN>

If column H (Operator) equals to “Not Built”, then in column A do the following</SPAN>

  • Grab Prefix value (Value entered in by user variable is uPrefix)</SPAN>
  • Model Number 3uu (u is for Unknown model)</SPAN>
  • Grab 6 digit CN from column B</SPAN>
  • To form A3uu000206 </SPAN>

If column H does not contain “Not Built” ignore and move on to next row.</SPAN>

Here is how the data looks in the spreadsheet before I run the code</SPAN>
c/n</SPAN></SPAN>
l/n</SPAN></SPAN>
f/f</SPAN></SPAN>
testreg</SPAN></SPAN>
regi</SPAN></SPAN>
operator</SPAN></SPAN>
d/d</SPAN></SPAN>
type</SPAN></SPAN>
St</SPAN></SPAN>
001</SPAN></SPAN>
28.10.72 </SPAN></SPAN>
F-WUAB </SPAN></SPAN>
F-OCAZ </SPAN></SPAN>
Airbus Industrie </SPAN></SPAN>
300B1</SPAN></SPAN>
STD</SPAN></SPAN>
002</SPAN></SPAN>
05.02.73</SPAN></SPAN>
F-WUAC </SPAN></SPAN>
OO-TEF</SPAN></SPAN>
TEA lsd</SPAN></SPAN>
25.11.74 </SPAN></SPAN>
300B1</SPAN></SPAN>
Air Algerie slsd </SPAN></SPAN>
25.11.74 </SPAN></SPAN>
Air Inter slsd </SPAN></SPAN>
07.01.80 </SPAN></SPAN>
Air Algerie slsd </SPAN></SPAN>
20.09.80 </SPAN></SPAN>
TEA bt </SPAN></SPAN>
06.01.87 </SPAN></SPAN>
SCR</SPAN></SPAN>
003</SPAN></SPAN>
28.06.73</SPAN></SPAN>
F-WUAD </SPAN></SPAN>
F-ODCX </SPAN></SPAN>
Airbus Industrie rr</SPAN></SPAN>
75</SPAN></SPAN>
300B2-103</SPAN></SPAN>
F-BUAD </SPAN></SPAN>
Airbus Industrie rr</SPAN></SPAN>
78</SPAN></SPAN>
General Electric lsd </SPAN></SPAN>
01.89 </SPAN></SPAN>
Sogerma</SPAN></SPAN>
30.05.96 </SPAN></SPAN>
SA Novespace</SPAN></SPAN>
25.01.05</SPAN></SPAN>
STD</SPAN></SPAN>
004</SPAN></SPAN>
20.11.73</SPAN></SPAN>
F-WUAA </SPAN></SPAN>
F-BUAE </SPAN></SPAN>
Air Inter</SPAN></SPAN>
22.01.77 </SPAN></SPAN>
300B2-1C </SPAN></SPAN>
STD</SPAN></SPAN>
005</SPAN></SPAN>
15.04.74</SPAN></SPAN>
F-WVGA </SPAN></SPAN>
F-BVGA </SPAN></SPAN>
Air France </SPAN></SPAN>
10.05.74 </SPAN></SPAN>
300B2-101</SPAN></SPAN>
SCR</SPAN></SPAN>
006</SPAN></SPAN>
23.06.74</SPAN></SPAN>
F-WVGB </SPAN></SPAN>
F-BVGB </SPAN></SPAN>
Air France </SPAN></SPAN>
28.06.74 </SPAN></SPAN>
300B2-101</SPAN></SPAN>
SCR</SPAN></SPAN>
007</SPAN></SPAN>
06.08.74</SPAN></SPAN>
F-WVGC </SPAN></SPAN>
F-BVGC </SPAN></SPAN>
Air France </SPAN></SPAN>
10.08.74 </SPAN></SPAN>
300B2-101</SPAN></SPAN>
SCR</SPAN></SPAN>

<TBODY>
</TBODY>

Here is how the code looks after I run the code and it’s then ready for import in the database. This is starting at column A below.</SPAN>
Unique</SPAN></SPAN>
CN</SPAN></SPAN>
Hist</SPAN></SPAN>
LN</SPAN></SPAN>
FF</SPAN></SPAN>
TestReg</SPAN></SPAN>
Rego</SPAN></SPAN>
Operator</SPAN></SPAN>
DD</SPAN></SPAN>
Category</SPAN></SPAN>
Type</SPAN></SPAN>
Status</SPAN></SPAN>
A300000001</SPAN></SPAN>
1</SPAN></SPAN>
001</SPAN></SPAN>
28.10.72 </SPAN></SPAN>
F-WUAB </SPAN></SPAN>
F-OCAZ </SPAN></SPAN>
Airbus Industrie </SPAN></SPAN>
Airliner Jet</SPAN></SPAN>
A300B1</SPAN></SPAN>
STD</SPAN></SPAN>
A300000002 </SPAN></SPAN>
2</SPAN></SPAN>
001</SPAN></SPAN>
05.02.73</SPAN></SPAN>
F-WUAC </SPAN></SPAN>
OO-TEF</SPAN></SPAN>
TEA lsd</SPAN></SPAN>
25.11.74 </SPAN></SPAN>
Airliner Jet</SPAN></SPAN>
A300B1</SPAN></SPAN>
A300000002 </SPAN></SPAN>
2</SPAN></SPAN>
002</SPAN></SPAN>
OO-TEF</SPAN></SPAN>
Air Algerie slsd </SPAN></SPAN>
25.11.74 </SPAN></SPAN>
Airliner Jet</SPAN></SPAN>
A300B1</SPAN></SPAN>
A300000002 </SPAN></SPAN>
2</SPAN></SPAN>
003</SPAN></SPAN>
OO-TEF</SPAN></SPAN>
Air Inter slsd </SPAN></SPAN>
07.01.80 </SPAN></SPAN>
Airliner Jet</SPAN></SPAN>
A300B1</SPAN></SPAN>
A300000002</SPAN></SPAN>
2</SPAN></SPAN>
004</SPAN></SPAN>
OO-TEF</SPAN></SPAN>
Air Algerie slsd </SPAN></SPAN>
20.09.80 </SPAN></SPAN>
Airliner Jet</SPAN></SPAN>
A300B1</SPAN></SPAN>
A300000002</SPAN></SPAN>
2</SPAN></SPAN>
005</SPAN></SPAN>
OO-TEF</SPAN></SPAN>
TEA bt </SPAN></SPAN>
06.01.87 </SPAN></SPAN>
Airliner Jet</SPAN></SPAN>
A300B1</SPAN></SPAN>
SCR</SPAN></SPAN>
A300000003</SPAN></SPAN>
3</SPAN></SPAN>
001</SPAN></SPAN>
28.06.73</SPAN></SPAN>
F-WUAD </SPAN></SPAN>
F-ODCX </SPAN></SPAN>
Airbus Industrie rr</SPAN></SPAN>
75</SPAN></SPAN>
Airliner Jet</SPAN></SPAN>
A300B2-103</SPAN></SPAN>
A300000003</SPAN></SPAN>
3</SPAN></SPAN>
002</SPAN></SPAN>
F-BUAD </SPAN></SPAN>
Airbus Industrie rr</SPAN></SPAN>
78</SPAN></SPAN>
Airliner Jet</SPAN></SPAN>
A300B2-103</SPAN></SPAN>
A300000003 </SPAN></SPAN>
3</SPAN></SPAN>
003</SPAN></SPAN>
F-BUAD </SPAN></SPAN>
General Electric lsd </SPAN></SPAN>
01.89 </SPAN></SPAN>
Airliner Jet</SPAN></SPAN>
A300B2-103</SPAN></SPAN>
A300000003</SPAN></SPAN>
3</SPAN></SPAN>
004</SPAN></SPAN>
F-BUAD </SPAN></SPAN>
Sogerma</SPAN></SPAN>
30.05.96 </SPAN></SPAN>
Airliner Jet</SPAN></SPAN>
A300B2-103</SPAN></SPAN>
A300000003</SPAN></SPAN>
3</SPAN></SPAN>
005</SPAN></SPAN>
F-BUAD </SPAN></SPAN>
SA Novespace</SPAN></SPAN>
25.01.05</SPAN></SPAN>
Airliner Jet</SPAN></SPAN>
A300B2-103</SPAN></SPAN>
STD</SPAN></SPAN>
A300000004</SPAN></SPAN>
4</SPAN></SPAN>
001</SPAN></SPAN>
20.11.73</SPAN></SPAN>
F-WUAA </SPAN></SPAN>
F-BUAE </SPAN></SPAN>
Air Inter</SPAN></SPAN>
22.01.77 </SPAN></SPAN>
Airliner Jet</SPAN></SPAN>
A300B2-1C </SPAN></SPAN>
STD</SPAN></SPAN>
A300000005</SPAN></SPAN>
5</SPAN></SPAN>
001</SPAN></SPAN>
15.04.74</SPAN></SPAN>
F-WVGA </SPAN></SPAN>
F-BVGA </SPAN></SPAN>
Air France </SPAN></SPAN>
10.05.74 </SPAN></SPAN>
Airliner Jet</SPAN></SPAN>
A300B2-101</SPAN></SPAN>
SCR</SPAN></SPAN>
A300000006</SPAN></SPAN>
6</SPAN></SPAN>
001</SPAN></SPAN>
23.06.74</SPAN></SPAN>
F-WVGB </SPAN></SPAN>
F-BVGB </SPAN></SPAN>
Air France </SPAN></SPAN>
28.06.74 </SPAN></SPAN>
Airliner Jet</SPAN></SPAN>
A300B2-101</SPAN></SPAN>
SCR</SPAN></SPAN>
A300000007</SPAN></SPAN>



7</SPAN></SPAN>
001</SPAN></SPAN>
06.08.74</SPAN></SPAN>
F-WVGC </SPAN></SPAN>
F-BVGC </SPAN></SPAN>
Air France </SPAN></SPAN>
10.08.74 </SPAN></SPAN>
Airliner Jet</SPAN></SPAN>
A300B2-101</SPAN></SPAN>
SCR</SPAN></SPAN>

<TBODY>
</TBODY>

Code: 
Sub ProcessAircraftFile()
'******************************************************************************
'This macro is for processing the excel aircraft files from [URL="http://www.airliners.com"]www.airliners.com[/URL]
'for eventual import into Access database.
'by James Niven 02/25/2009
'http://www.vbaexpress.com/forum/showthread.php?25887-Solved-Further-Help-with-Code&highlight
'******************************************************************************
'Declare Variables
    Dim lngLastRow As Long, lngLastColumn As Long
    Dim wksProdList As Worksheet
    Dim NotBuilt$
    Dim Nil$
    Dim uPrefix As String
    Dim arrK As Variant, i As Long
'Set sheet Name
    Set wksProdList = Worksheets("ProdList")
'Set NotBuilt options
    NotBuilt = """" & "NOT BUILT" & """"
        Nil = """"""
        With wksProdList
'Set Model Prefix
    uPrefix = InputBox("Enter in Single letter Prefix?", "Aircraft Prefix")
    
 
'Find last used row & col
    lngLastRow = .Cells.Find(What:="*", After:=.Cells(1, 1), SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    lngLastColumn = .Cells.Find(What:="*", After:=.Cells(1, 1), SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
'Insert New Column at A
    Range("A1").EntireColumn.Insert
    Cells(3, 1).Value = "Unique"
'CN Column
    .Columns("B").TextFormat = "000000"
    With .Range("B2:B" & lngLastRow)
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
    Columns("B").EntireColumn.AutoFit
End With
'Row Column
    .Columns("C").NumberFormat = "000"
    With .Range("C3")
    .FormulaR1C1 = "=IF(RC[-1]<>R[-1]C[-1],1,R[-1]C+1)"
    .AutoFill Destination:=Range("C3:C" & lngLastRow), Type:=xlFillDefault
    Columns("C").EntireColumn.AutoFit
End With
'Rego Column
    With .Range("G4:G" & lngLastRow)
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=if(RC[1]<>" & NotBuilt & ",R[-1]C," & Nil & ")"
    .Value = .Value
End With
'Operator Column if "Not Built" is present
    'With .Range("H4:H" & lngLastRow)
    '.FormulaR1C1 = "=if(RC[1]<>" & Not Built & ",R[-1]C," & Nil & ")"
    '.Value = .Value
'End With
'Insert New Column at J
    Range("J4").EntireColumn.Insert
    Cells(3, 10).Value = "Category"
    With .Range("J4:J" & lngLastRow)
    .Value = "Airliner Jet"
    Columns("J").EntireColumn.AutoFit
End With

'Type2 Column
     With Range("K4:K" & lngLastRow)
     .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(RC[-3]<>" & NotBuilt & ",R[-1]C," & Nil & ")"
        arrK = .Value
        For i = LBound(arrK) To UBound(arrK)
            arrK(i, 1) = uPrefix & arrK(i, 1)
        Next i
        .Value = arrK
        Columns("K").EntireColumn.AutoFit
End With
'Make up Unique Key in Column A
    With .Range("A4:A" & lngLastRow)
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=LEFT(RC[10],4)&TEXT(LEFT(RC[1],6),""000000"")"
    .Value = .Value
    Columns("A").EntireColumn.AutoFit
End With
End With

End Sub
 
Last edited:

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
Anyone....

I am looking for some help here.</SPAN>
At present the aircrafts Not Built from their production lists are being missed by the code I have shown below. Below I have outlined what I want to do.</SPAN>
I have posted all the code to show you want I am doing, I only want to add a portion for column H “Operator Column”.</SPAN>

If column H (Operator) equals to “Not Built”, then in column A do the following</SPAN>


  • Grab Prefix value (Value entered in by user variable is uPrefix)</SPAN>
  • Model Number 3uu (u is for Unknown model)</SPAN>
  • Grab 6 digit CN from column B</SPAN>
  • To form A3uu000206 </SPAN>


If column H does not contain “Not Built” ignore and move on to next row. I have come up with the following code

Code: 
'Operator Column if "Not Built" is present
    With Target.Range("H4:H" & Lastrow)
    If Target.Value = "Not Built" Then
    .FormulaR1C1 = "A3uu&TEXT(LEFT(RC[1],6),""000000"")"
    Else
'End If
'Insert New Column at J
    Range("J4").EntireColumn.Insert
    Cells(3, 10).Value = "Category"
    With .Range("J4:J" & lngLastRow)
    '.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = "Airliner Jet"
    Columns("J").EntireColumn.AutoFit
End With

'Type2 Column
     With Range("K4:K" & lngLastRow)
     .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(RC[-3]<>" & NotBuilt & ",R[-1]C," & Nil & ")"
        arrK = .Value
        For i = LBound(arrK) To UBound(arrK)
            arrK(i, 1) = uPrefix & arrK(i, 1)
        Next i
        .Value = arrK
        Columns("K").EntireColumn.AutoFit
End With
'Make up Unique Key in Column A
    With .Range("A4:A" & lngLastRow)
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=LEFT(RC[10],4)&TEXT(LEFT(RC[1],6),""000000"")"
    
    '.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=LEFT(RC[10],4)&""-""&TEXT(LEFT(RC[1],6),""000000"")"
    .Value = .Value
    Columns("A").EntireColumn.AutoFit
End With
End With

End Sub

Here is a sample of the spreadsheet

222</SPAN>22.11.82</SPAN>F-WZMA </SPAN>9V-STF </SPAN>Singapore AL </SPAN>23.12.82</SPAN>300B4-203</SPAN>
ZS-SDH </SPAN>South African AL </SPAN>24.04.85</SPAN>
RP-C3006 </SPAN>Philippine AL lsd</SPAN>10.05.87</SPAN>
ZS-SDH </SPAN>South African AL rt</SPAN>s4.04.96</SPAN>
TC-MNE</SPAN>MNG AL</SPAN> 05.01</SPAN>
Turkish AL lsd</SPAN>03.01.05</SPAN>
MNG AL ret</SPAN>00.03.05</SPAN>
223</SPAN>NOT BUILT</SPAN>
224</SPAN>05.01.83</SPAN>F-WZEA </SPAN>HB-IPA </SPAN>Swissair </SPAN>25.03.83</SPAN>310-221</SPAN>
N446FE </SPAN>Federal Express</SPAN>r10.95</SPAN>*F</SPAN>
225</SPAN>16.12.82</SPAN>F-WZMB </SPAN>PP-SNN </SPAN>VASP </SPAN>31.01.83</SPAN>300B2-203</SPAN>
LABoliviano lsd</SPAN> 06.90</SPAN>
VASP ret </SPAN> 06.91</SPAN>
226</SPAN>13.12.82</SPAN>F-WZME </SPAN>EP-IBZ </SPAN>Iran Air </SPAN>31.01.83</SPAN>300B2-203</SPAN>
227</SPAN>01.03.83</SPAN>F-WZMC </SPAN>N203PA </SPAN>Pan Am </SPAN>21.12.84</SPAN>300B4-203</SPAN>
Carnival AL</SPAN>s20.12.94</SPAN>
LADECO lsd </SPAN>s1.5.95 </SPAN>
N223KW </SPAN>Carnival AL rr </SPAN>s27.12.95</SPAN>*F</SPAN>
EC-HVZ</SPAN>TNT AW</SPAN>17.02.01</SPAN>
N227TN</SPAN>WFBN</SPAN>04.08.09</SPAN>
AeroUnion lsd</SPAN>01.08.10</SPAN>
XA-FPP</SPAN>AeroUnion rr</SPAN>19.08.10</SPAN>
228</SPAN>NOT BUILT</SPAN>
229</SPAN>NOT BUILT</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL span=2><COL><COL><COL></COLGROUP>

Thanks

</SPAN>
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,214,645
Messages
6,120,711
Members
448,984
Latest member
foxpro

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top