Solved: Help with VBA code

[James Niven]

I am looking for some help on this project. I am putting VBA code together to run against this spreadsheet which in this example show a partial history of different Airbus airframes. Column A shows the construction number of the aircraft.

What I wish to do is in column A, when a new construction number starts the row count in column B will begin at 01 and go till a new construction number is discovered. I wish to run code on column B that will fill in the numbers just as I have in yellow all the way down to the end of the spreadsheet.

I will use this row number to keep the history in chronological order of Delivery Date, Column H.

As you can see, I cannot format this as a date format because of unknown information otherwise I would sort on date.

My example is attached to this message.

All this information you see above will be going into an Access database.
Can someone offer a solution to my issue!!

Thanks to those who stop by and offer help.

James Niven
Cedar Creek, Texas

[Kenneth Hobs]

B3 would be: =IF(A3<>A2,1,B2+1)
Set a custom number format of "00#".

This could be automated via a Change event if needed.

You can use ADO to insert your records. e.g.
Sub demo()
Dim objRS As Object, nwindPath As String
Set objRS = CreateObject("ADODB.Recordset")
nwindPath = ThisWorkbook.Path & "\nwind.mdb"

Dim r As Range
[a1] = "LastName"
[b1] = "FirstName"
[a2] = "Hobson"
[b2] = "Kenneth"
Set r = [a1:b2]
r.Name = "MyRange"

objRS.Open "INSERT INTO Employees SELECT * FROM [MyRange] IN '" & ThisWorkbook.FullName & "' 'Excel 8.0;'", _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & nwindPath

Set objRS = Nothing
End Sub
You can set r to the Worksheets("Sheet1").UsedRange rather than [a1:b2]. Of course you need to set your mdb table path and names and column names to fit your setup.

[GTO]

Edit: Deleted; (jeepers!) didn't realize how long since refreshed and much better answer by Ken :-)

[James Niven]

Hi Kenneth,

Thanks for the demo code and quick reply.
I am new to VBA code and don't fully understand all you have typed.

I may have not made myself clear. The data on this spreadsheet will not go into the database until way down the track and I have the spreadsheet the way I want. I still have other formatting and code to work on before this is ready for import into the database.

I notice some of the code example refers to the database. I at this point just need some code for working on the spreadsheet. I did look at the =IF(A3<>A2,1,B2+1) and tried this by entering in the column B and drag it down but it gave me 001 all the way down the column, this is not what I want.

Can you offer anther demo example.

Thanks

James Niven
Cedar Creek, TX

[Bob Phillips]

It gave me an incrementing number. Have you set manual calculation?

[James Niven]

Hi XLD,

What do you mean by;

"It gave me an incrementing number. Have you set manual calculation?"

What is manual calculation?

Thanks

[Bob Phillips]

I mean that when the key was duplicated, your formula incremented as it should. Manual Calculation is an option in Tools>Options, turn it off, to automatic.

[James Niven]

XLD,

That does not work for some reason.
I checked the settings as you discussed.

:banghead:

Thanks

[GTO]

Greetings and a Howdy from Arizona James,

Could you confirm/check the following:

A) The workbook you are testing in is layed out the same as the example workbook you attached. That is, on "Sheet1" row 1 is a "header" row, Column A is "c/n" and cell A2 starts the "c/n" vals; Column B is "Row" starting at B2, etc.

B) You are starting the formula in cell B3

Then the formula would go (without the quote marks):
In Cell B3: "=IF(A3<>A2,1,B2+1)"
In Cell B4: "=IF(A4<>A3,1,B3+1)"
In Cell B5: "=IF(A5<>A4,1,B4+1)"
...and so on...

Hopefully I am not being too explicit, but I figure it has to be a silly/simple oversight (which I certainly have had my share of :*) ).

Hope that helps,

Mark

[James Niven]

Mark,

Thank you, you were very clear in your discription, and it worked doing exactly as you layout.

I was starting the formula in B2, hence the formaula would not work when pulled down to other cells.

OK, I am over this hurdle, so I now wish to do this in code on the whole column B of the spreadsheet till the last row of data?
I want to try and work this out.

Thanks for the help offered

James

[James Niven]

Mark,

I have got so far with this trying most of today and hitting a brick wall.

I could use some assistance with this code on my example.

Thanks

James

[GTO]

Greetings James,

Firstly, you mentioned that you had started the formula for column 2 (B) in the wrong cell.
The only reason I ask about this is that I would have thought that Excel would have given
you a big ol' warning about circular references. Did this not occur?

I did take a look at your code from the wb posted at #11. I believe that I've commented it
up fairly clearly, but if anything needs better explained, please don't hesitate to ask.


Sub ProcessAircraftFile()

'Declare Variables
Dim shname As String
'// Lets add only the variables we are currently using in the existing code. This //
'// saves memory and (IMO) just as importantly, makes the code more //
'// manageable/readable. Next - when dimensioning variables, your first line only //
'// makes 'I' a Long; 'ThisC', 'ThisR', etc, become Variants by default. //
' Dim ThisC, ThisR, FirstR, LastR, NextR, LastC, I As Long
' Dim lngI, lngJ As Long
' Dim clr1, clr2, clr3 As String

Dim LastR As Long, LastC As Long
Dim wksProdList As Worksheet

'Select Active Worksheet
shname = "Prodlist"
'// Lets set a reference to the sheet rather than depend upon its being active. //
'// This way, we have much better control over what we are doing with/to the sheet, //
'// as we have defined the sheet as an object, rather than just working with what //
'// sheet happens to have the focus. //
'Worksheets(shname).Activate
Set wksProdList = ThisWorkbook.Worksheets("ProdList")

'Color Hierarchy
'// Not currently used, but these should be Longs I believe. Also, since the value of //
'// 'clr1' for instance, will not change, these could be Constants. //
' clr1 = 33 'red
' clr2 = 2 'White
' clr3 = 4 'Blue

'find last used row & col
With wksProdList

LastR = .Cells.Find(What:="*", After:=.Cells(1, 1), _
SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
LastC = .Cells.Find(What:="*", After:=.Cells(1, 1), _
SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column


'CN Column
'// As I can tell, we are looking to fill in the blanks in Column 1 here, so lets //
'// use 'LastR' so that the blanks below the '9' (rows 23-28) will also be filled in//
'// Note that while we are not setting a reference to the range for these few lines,//
'// we will better qualify the range as belonging to wksProdList //
'With Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
With .Range("A2:A" & LastR)
'// I saw no reason to turn off error checking here. I would suggest against //
'// ignoring errors except when allowing errors to be raised for a good //
'// purpose, such as checking the existence of an object such as a sheet, or, //
'// when what you are doing requires ignoring errors, such as when building a //
'// collection of unique vals. In short - turning off error checking without a //
'// specific reason can mask problems with the code. //
'On Error Resume Next
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
'On Error GoTo 0
.Value = .Value
.Font.Bold = True
.Interior.ColorIndex = 8
End With

.Columns("A").NumberFormat = "000"

'Row Column
.Columns("B").NumberFormat = "00"

' With Range("B3")
' ActiveCell.FormulaR1C1 = "=IF(RC[-1]<>R[-1]C[-1],1,R[-1]C+1)"
' Range("B3").Select
' Selection.AutoFill Destination:=Range("B"), Type:=xlFillDefault
' End With

With .Range("B3")
.FormulaR1C1 = "=IF(RC[-1]<>R[-1]C[-1],1,R[-1]C+1)"
.AutoFill Destination:=Range("B3:B" & LastR), Type:=xlFillDefault
End With
End With
End Sub
or:
Sub ProcessAircraftFile()
'Declare Variables
'// To simplify example a bit...//
Dim lngLastRow As Long, lngLastColumn As Long
Dim wksProdList As Worksheet

'// If using constants for color(s), maybe...//
Const TURQUOISE As Long = 16776960

Set wksProdList = ThisWorkbook.Worksheets("ProdList")

With wksProdList

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

With .Range("A2:A" & lngLastRow)
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
.Value = .Value
.Font.Bold = True
.Interior.Color = TURQUOISE
End With

.Columns("A").NumberFormat = "000"
.Columns("B").NumberFormat = "00"

With .Range("B3")
.FormulaR1C1 = "=IF(RC[-1]<>R[-1]C[-1],1,R[-1]C+1)"
.AutoFill Destination:=Range("B3:B" & lngLastRow), Type:=xlFillDefault
End With
End With
End Sub

Hope this helps :)

Mark

[James Niven]

Hi Mark,

You make some very valid points, I am a very raw beginner to VBA, so thanks for begin gentle and not too critical - we all started somewhere.

Thanks for the solution to my problem, works like a charm indeed.

I know I have lots too learn and we have great teachers out there on the forum to steer us in the right diection.

Thanks to all for assistance offered, its a learning curve.
I have begin using the recording a macro feature in excel, this does help form some of the code, I am learning from that direction also as well as reading. Does any recommend any good books or online training on VBA that will help broaden my knowledge?

Thanks

James

[James Niven]

Mark,

I forgot to mention in my previous message, Yes, I did have circular error when the formula was dropped in the wrong cell.

Thanks

James

[GTO]

Hi Mark,

You make some very valid points, I am a very raw beginner to VBA, so thanks for begin gentle and not too critical - we all started somewhere.

Thanks for the solution to my problem, works like a charm indeed.

I know I have lots too learn and we have great teachers out there on the forum to steer us in the right diection.

Does any recommend any good books or online training on VBA that will help broaden my knowledge?


You are very welcome and glad to help:friends: . As one possessing rather rudimentary knowledge myself, I sure agree as to some great teachers here, from whom I continue to learn almost daily.

I'm afraid I don't have any good suggestions as to books, hopefully someone else will. Personally what knowledge I've gained has been through here, sometimes googling a paricular thing, of course the app's help files, and having the good fortune of becoming friends with a buddy who is particularly bright...

As to the recorder, I certainly find it a handy tool, particularly as a 'cheat' to remind me of args for functions. I think that you will quickly find though, that your end product will most always be considerably different than what the recorder lays down. I forgot to mention this before, but an easy example could be autofilling:

Recorded:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 03/20/2009 by stumpm
'
'
ActiveCell.FormulaR1C1 = "1"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A18"), Type:=xlFillDefault
Range("A2:A18").Select
Range("D13").Select
End Sub

Now besides the unnecessary ActiveCell/Select/Selection, I forgot to mention that if A1 notation seems more intuitive, we could do the same thing in column B like:
Sub mymac()
Range("B1").Value = 1
Range("B2").Formula = "=B1+1"
Range("B2").AutoFill Destination:=Range("B2:B18"), Type:=xlFillDefault
End Sub

Anyways, thought to mention, and again, glad to be of what help I can.

Happy coding!

Mark

PS - Under Thread Tools above your first post, there is a Mark Solved button. If solved, this helps others not to keep checking a thread's status.

[James Niven]

Hi Mark,

Thanks for your friendly input to solve my issue, I thank you.

Yes, I have seen exactly what you refer to with record the macros, the code is a little different, but a nice place to begin.

Thank, I look forward to further communication down the track.

Thanks

James Niven