PDA

View Full Version : Combo Box Pick assigned to next empty row



ameritecc
03-04-2008, 05:33 PM
Below is code from one of my workbook forms that takes material from a combo box that the user picks and places it on the next row down on a takeoff sheet. I have 13 takeoff sheets that have the exact same rows and columns, yet on a couple of my takeoff sheets the material skips a line and leaves a row space between the new entry and the existing. 11 of these sheets do it properly using the same code. Also the material should be placed starting on row 13, and as you can see what I have written indicates row 11. I had to play with this to make it work, and I have no idea why it does apply the material starting at row 13. Using the number 13 placed it above row 13, and into headers I have on the sheet. I have exactly 183 rows that the user cannot go beyond, so I set this up this way to prevent a user from trying to go below this. Could someone please review this code and tell me if there is more precise way to eliminate the row space created on 2 of the worksheets, and also why 11 works for row 13, instead of using 13 in the code. Would an xldown procedure be better? If so please correct my code, because I don't know how to apply it for use in 183 rows only. The code in question is underlined here only for quicker spotting.



MY VBA Code:
Private Sub CommandButton1_Click()
'Add button
Dim NextRow As Integer

'Make sure Material sheet is active and determine next empty row
Sheets("FOB").Activate
NextRow = Application.WorksheetFunction.CountA(Range("M:N")) + 11
Cells(Rows.Count, 183).End(xlUp)(1).Select
'Transfer info to empty row
'Cells(NextRow, 3) = TextBox1.Text 'weight per foot
Cells(NextRow, 14) = TextLength.Text 'length
Cells(NextRow, 13) = ComboBox2 'material item number
Cells(NextRow, 1) = ComboBox3 'Item Identification
Cells(NextRow, 11) = TextBox2.Text 'Qty of main piece
Cells(NextRow, 12) = TextBox3.Text 'Qty of Subpiece

'Load Cost Unit of Measure based on item type: all types are
'charged by linear foot except Channel, Wide Flange, S Shape
'which are charged by hundred weight, and Plate which is by
'square foot
Select Case ComboBox1
Case "Stiffeners", "Std. Framing Angles", "Shear Plates", "Buyouts"
Cells(NextRow, 14) = ""
Cells(NextRow, 1) = "STDPART"
'Case "Wide Flange", "S Shape", "Channel"
'Cells(NextRow, 6) = "CWT"
'Case "Plate"
'Cells(NextRow, 6) = "SFT"
Case Else
'Cells(NextRow, 6) = "LFT"
End Select

'Clear weight, length, and item
TextBox1.Text = ""
TextLength.Text = ""
ComboBox2 = ""
End Sub

Simon Lloyd
03-04-2008, 05:51 PM
I havent checked anything else but this line:

Cells(Rows.Count, 183).End(xlUp)(1).Select

only counts the rows and finds the last in column GA, because the annotation you are using Cells(x,x) is actually Cells(RowIndex, ColumnIndex)

Simon Lloyd
03-04-2008, 05:59 PM
You probably wanted this:

Range(Cells(1, 1), Cells(183, 1).End(xlUp)(1)).Select