Solved: Further Help with Code

[James Niven]

Hi All,

I have come to ask for further help with my excel project, I have gone as far as my knowledge will take me at the moment.

GTO (Mark) was very gracious enough to help me on my last cry for help!!

Here are my 2 issue I am trying to work around.

If you will look at the attached file and run the code it runs as wanted on the columns selected.

1. I have looked at the columns in particular "G" and I have noticed if there is "Not Built" somewhere in column "H" it will fill in a gistration from the cell above which I don't want it to do. How do I get around that issue?

2. I have looked at the columns in particular "K" and I have noticed if there is "Not Built" somewhere in column "H" it will fill in aircraft type from the cell above which I don't want it to do. How do I get around that issue?
Also, in this column "K" if an aircraft type is converted from passanger model to freighter model, there will only be and *F in the column when the conversion place. I want some way to add the F to the end of the aircraft type in the cell above.

So eg: On line 44 on the attached spreadsheet you have 300B4-203 and the next line is *F, I want 300BF-203F until a new type is encountered.

I hope I have explained myself clearly.

Thanks

James Niven

Cedar Creek

[James Niven]

Hi,

I have no bites on this question, any takers?

Thanks

James Niven
Cedar Creek, TX

[mdmackillop]

A couple of points.
With .Range("J4:J" & lngLastRow)
'Why set formula and then overwrite? @@@@
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
.Value = "Jet Airliner"
.Font.Bold = True


'Make up Unique Key in Column A
With .Range("A4:A" & lngLastRow)
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=LEFT(RC[1],3)&""-""&LEFT(RC[10],3)"
'Results are not unique @@@@@
.Value = .Value
.Font.Bold = True

[mdmackillop]

For G, similar for K

'Rego Column
Dim NotBuilt$
Dim Nil$
NotBuilt = """" & "NOT BUILT" & """"
Nil = """"""
With .Range("G2:G" & lngLastRow)
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=if(RC[1]<>" & NotBuilt & ",R[-1]C," & Nil & ")"
.Value = .Value
.Font.Bold = True

End With

[James Niven]

Hi mdmackillop,

Thanks for the replies, and assistance.

Your additional code for column G works exactly as wanted, thanks.

You then say use the same code for Column K, I cannot get this to work, it will still fill in Aircraft type next to enteries where Column H has Not Built in the cell.

I have looked at your code line, but unsure what to change.
Also any idea's on my 2nd question I ask?

As for Column A, trying to make the values unique, this excel file will be imported into an Access database where the file will be split between two tables, hence the reason for the unique entry here. The first line of each entry will be in one table and the remaining lines will be a foregin key showing history of this construction number, like a link between the two.

Thanks

James Niven
Cedar Creek

[mdmackillop]

Option Explicit
Sub ProcessAircraftFile()
'************************************************************************** ***
'This macro is for processing the excel aircraft files
'for eventual import into Access database.
'
'by James Niven 02/25/2009
'************************************************************************** ***
'Declare Variables
Dim lngLastRow As Long, lngLastColumn As Long
Dim wksProdList As Worksheet
Dim NotBuilt$
Dim Nil$

'Set sheet Name
Set wksProdList = Worksheets("ProdList")
'Set NotBuilt options
NotBuilt = """" & "NOT BUILT" & """"
Nil = """"""
With wksProdList

'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 = "MSNID"

'CN Column
.Columns("B").NumberFormat = "000"
With .Range("B2:B" & lngLastRow)
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
.Value = .Value
.Font.Bold = True

End With

'Row Column
.Columns("C").NumberFormat = "00"
With .Range("C3")
.FormulaR1C1 = "=IF(RC[-1]<>R[-1]C[-1],1,R[-1]C+1)"
.AutoFill Destination:=Range("C3:C" & lngLastRow), Type:=xlFillDefault

End With
'Rego Column
With .Range("G2:G" & lngLastRow)
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=if(RC[1]<>" & NotBuilt & ",R[-1]C," & Nil & ")"
.Value = .Value
.Font.Bold = True

End With
'Insert New Column at J
Range("J1").EntireColumn.Insert
Cells(3, 10).Value = "Category"
With .Range("J4:J" & lngLastRow)
'.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
.Value = "Jet Airliner"
.Font.Bold = True

End With
'Type Column
With .Range("K2:K" & lngLastRow)
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=if(RC[-3]<>" & NotBuilt & ",R[-1]C," & Nil & ")"
.Value = .Value
.Font.Bold = True

End With

'Make up Unique Key in Column A
With .Range("A4:A" & lngLastRow)
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=LEFT(RC[1],3)&""-""&LEFT(RC[10],3)"
.Value = .Value
.Font.Bold = True

End With

End With
End Sub

[James Niven]

Hi mdmackillop,

That worked as I wanted, can you please explain how this line works?

'Type Column
FormulaR1C1 = "=if(RC[-3]<>" & NotBuilt & ",R[-1]C," & Nil & ")"

Just trying to understand!

Thanks for your assistance on my project!!

James Niven
Cedar Creek

[mdmackillop]

The R1C1 reference style allows either absolute or relative references. Relative references are the numbers in brackets so RC[-3] means same row, 3 columns to the left. RC3 would be same row, Column C. R2C2 would be B2. and so on. In this case it creates the If statement you will see in the cells.

[James Niven]

Hi mdmackillop,

You have explained this very clearly, I now understand this line pretty well.

Thanks for your assistance.

James Niven
Cedar Creek