Emoncada
01-17-2013, 10:50 AM
I have the following code
Private Sub CmdUpdate_Click()
'Add New Button to your UserForm so you press to Apply Data to Sheet
Dim RowNext1 As Integer, RowNext2 As Integer, RowNext3 As Integer
Dim RowNext4 As Integer, RowNext5 As Integer, RowNext6 As Integer
' etc to Dim RowNext15 As Integer....
'last row of data puls one row
RowNext1 = Worksheets("Unreported").Cells(65536, 1).End(xlUp).Row + 1
RowNext2 = Worksheets("Unreported").Cells(65536, 1).End(xlUp).Row + 2
RowNext3 = Worksheets("Unreported").Cells(65536, 1).End(xlUp).Row + 3
RowNext4 = Worksheets("Unreported").Cells(65536, 1).End(xlUp).Row + 4
RowNext5 = Worksheets("Unreported").Cells(65536, 1).End(xlUp).Row + 5
RowNext6 = Worksheets("Unreported").Cells(65536, 1).End(xlUp).Row + 6
'etc to RowNext15.....
With Worksheets("Unreported")
If UserForm2.chkPart78.Value = True Then
.Cells(RowNext1, 1) = Date
.Cells(RowNext1, 2) = UserForm2.chkPart78.Caption & " - " & UserForm2.CmbModel.Value
.Cells(RowNext1, 3) = UserForm2.TxtSerial.Value
.Cells(RowNext1, 4) = UserForm2.LblPartition.Caption
.Cells(RowNext1, 5) = "Type 5"
.Cells(RowNext1, 6) = Environ("Username")
End If
If UserForm2.chkFirm14.Value = True Then
.Cells(RowNext2, 1) = Date
.Cells(RowNext2, 2) = UserForm2.chkFirm14.Caption & " - " & UserForm2.CmbModel.Value
.Cells(RowNext2, 3) = UserForm2.TxtSerial.Value
.Cells(RowNext2, 4) = UserForm2.LblFirmware.Caption
.Cells(RowNext2, 5) = "Type 5"
.Cells(RowNext2, 6) = Environ("Username")
'etc.....
This works perfect transferring the data to the spreadsheet, but I need to modify it for one item. I added a txtbox for the user to enter a quantity when they select chkfirm14.
So I need something like this
if Userform2.chkfirm14.value = True Then
j = UserForm2.TxtDriveQty.Value
For Counter = 1 To j
.Cells(RowNext2, 1) = Date
.Cells(RowNext2, 2) = UserForm2.chkFirm14.Caption & " - " & UserForm2.CmbModel.Value
.Cells(RowNext2, 3) = UserForm2.TxtSerial.Value
.Cells(RowNext2, 4) = UserForm2.LblFirmware.Caption
.Cells(RowNext2, 5) = "Type 5"
.Cells(RowNext2, 6) = Environ("Username")
Next Counter
Problem with this is (RowNext2) is just 1 line so no matter the qty in txtDriveQty it just places 1 line.
I need something like RowNext2 + 1 for Counter.
Any Ideas?
Private Sub CmdUpdate_Click()
'Add New Button to your UserForm so you press to Apply Data to Sheet
Dim RowNext1 As Integer, RowNext2 As Integer, RowNext3 As Integer
Dim RowNext4 As Integer, RowNext5 As Integer, RowNext6 As Integer
' etc to Dim RowNext15 As Integer....
'last row of data puls one row
RowNext1 = Worksheets("Unreported").Cells(65536, 1).End(xlUp).Row + 1
RowNext2 = Worksheets("Unreported").Cells(65536, 1).End(xlUp).Row + 2
RowNext3 = Worksheets("Unreported").Cells(65536, 1).End(xlUp).Row + 3
RowNext4 = Worksheets("Unreported").Cells(65536, 1).End(xlUp).Row + 4
RowNext5 = Worksheets("Unreported").Cells(65536, 1).End(xlUp).Row + 5
RowNext6 = Worksheets("Unreported").Cells(65536, 1).End(xlUp).Row + 6
'etc to RowNext15.....
With Worksheets("Unreported")
If UserForm2.chkPart78.Value = True Then
.Cells(RowNext1, 1) = Date
.Cells(RowNext1, 2) = UserForm2.chkPart78.Caption & " - " & UserForm2.CmbModel.Value
.Cells(RowNext1, 3) = UserForm2.TxtSerial.Value
.Cells(RowNext1, 4) = UserForm2.LblPartition.Caption
.Cells(RowNext1, 5) = "Type 5"
.Cells(RowNext1, 6) = Environ("Username")
End If
If UserForm2.chkFirm14.Value = True Then
.Cells(RowNext2, 1) = Date
.Cells(RowNext2, 2) = UserForm2.chkFirm14.Caption & " - " & UserForm2.CmbModel.Value
.Cells(RowNext2, 3) = UserForm2.TxtSerial.Value
.Cells(RowNext2, 4) = UserForm2.LblFirmware.Caption
.Cells(RowNext2, 5) = "Type 5"
.Cells(RowNext2, 6) = Environ("Username")
'etc.....
This works perfect transferring the data to the spreadsheet, but I need to modify it for one item. I added a txtbox for the user to enter a quantity when they select chkfirm14.
So I need something like this
if Userform2.chkfirm14.value = True Then
j = UserForm2.TxtDriveQty.Value
For Counter = 1 To j
.Cells(RowNext2, 1) = Date
.Cells(RowNext2, 2) = UserForm2.chkFirm14.Caption & " - " & UserForm2.CmbModel.Value
.Cells(RowNext2, 3) = UserForm2.TxtSerial.Value
.Cells(RowNext2, 4) = UserForm2.LblFirmware.Caption
.Cells(RowNext2, 5) = "Type 5"
.Cells(RowNext2, 6) = Environ("Username")
Next Counter
Problem with this is (RowNext2) is just 1 line so no matter the qty in txtDriveQty it just places 1 line.
I need something like RowNext2 + 1 for Counter.
Any Ideas?