Hello all,
I am making a userform for data entry and I am wanting my data to go in the next empty row, however there is one column that contains a formula all the way down the spreadsheet. Is there a way to have my code go to the next empty row ignoring the formulas?
Here is what I have
Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("MSDS DATABASE")
'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
'check for a part number
If Trim(Me.txtreview.Value) = "" Then
Me.txtchem.SetFocus
MsgBox "Please enter a review date"
Exit Sub
End If
'copy the data to the database
'use protect and unprotect lines,
' with your password
' if worksheet is protected
With ws
' .Unprotect Password:="password"
.Cells(iRow, 1).Value = Me.txtchem.Value
.Cells(iRow, 2).Value = Me.txtform.Value
.Cells(iRow, 3).Value = Me.txtconc.Value
.Cells(iRow, 4).Value = Me.txtpro.Value
.Cells(iRow, 5).Value = Me.txtamount.Value
.Cells(iRow, 6).Value = Me.txtman.Value
.Cells(iRow, 7).Value = Me.txtlab.Value
.Cells(iRow, 9).Value = Me.txtreview.Value
.Cells(iRow, 11).Value = Me.txtpg.Value
' .Protect Password:="password"
End With
'clear the data
Me.txtchem.Value = ""
Me.txtform.Value = ""
Me.txtconc.Value = ""
Me.txtpro.Value = ""
Me.txtamount.Value = ""
Me.txtman.Value = ""
Me.txtlab.Value = ""
Me.txtreview.Value = ""
Me.txtpg.Value = ""
Me.txtchem.SetFocus
If Me.cbA.Value = True Then
ws.Cells(iRow, 8).Value = "A"
Else
ws.Cells(iRow, 8).Value = ""
End If
If Me.cbB.Value = True Then
ws.Cells(iRow, 8).Value = "B"
Else
ws.Cells(iRow, 8).Value = ""
End If
If Me.cbC.Value = True Then
ws.Cells(iRow, 8).Value = "C"
Else
ws.Cells(iRow, 8).Value = ""
End If
If Me.cbD1A.Value = True Then
ws.Cells(iRow, 8).Value = "D1A"
Else
ws.Cells(iRow, 8).Value = ""
End If
If Me.cbD1B.Value = True Then
ws.Cells(iRow, 8).Value = "D1B"
Else
ws.Cells(iRow, 8).Value = ""
End If
If Me.cbD2A.Value = True Then
ws.Cells(iRow, 8).Value = "D2A"
Else
ws.Cells(iRow, 8).Value = ""
End If
If Me.cbD2B.Value = True Then
ws.Cells(iRow, 8).Value = "D2B"
Else
ws.Cells(iRow, 8).Value = ""
End If
If Me.cbD3.Value = True Then
ws.Cells(iRow, 8).Value = "D3"
Else
ws.Cells(iRow, 8).Value = ""
End If
If Me.cbE.Value = True Then
ws.Cells(iRow, 8).Value = "E"
Else
ws.Cells(iRow, 8).Value = ""
End If
If Me.cbF.Value = True Then
ws.Cells(iRow, 8).Value = "F"
Else
ws.Cells(iRow, 8).Value = ""
End If
End Sub
Bookmarks