T
Tony
Not sure if my first one went through okay.
I am using a form to provide input into a spreadsheet. Every time I write a
record, I want it to write to a new line in the spreadsheet. When I first
used this code, it wrote on the second line in the spreadsheet, even though
there was a second header line there and it overwrote the line. When I took
out the first header, it wrote on the second line okay. However, when I tried
to enter a new record, it overwrote the first record.
Basically I want to write the first new record on the third line (after two
header lines) and then each new record on the next available line, etc. Below
is the code I am using. Can you someone help me out here?
Private Sub CommandButton8_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Master")
Dim nextrow As Long
'find first empty row in database
With Worksheets("Master")
nextrow = .Range("p65536").End(xlUp).Row + 1
End With
'check to see if there's room
If nextrow >= 6589 Then
MsgBox "out of room!"
Exit Sub
End If
With Worksheets("Master")
.Cells(nextrow, 1).Value = InquiryDate
.Cells(nextrow, 2).Value = DatabaseAccessed
.Cells(nextrow, 3).Value = Requestor
.Cells(nextrow, 4).Value = AccountAccessed
.Cells(nextrow, 5).Value = RequestReason
.Cells(nextrow, 6).Value = InformationRequested
.Cells(nextrow, 7).Value = Comments
End With
End Sub
I am using a form to provide input into a spreadsheet. Every time I write a
record, I want it to write to a new line in the spreadsheet. When I first
used this code, it wrote on the second line in the spreadsheet, even though
there was a second header line there and it overwrote the line. When I took
out the first header, it wrote on the second line okay. However, when I tried
to enter a new record, it overwrote the first record.
Basically I want to write the first new record on the third line (after two
header lines) and then each new record on the next available line, etc. Below
is the code I am using. Can you someone help me out here?
Private Sub CommandButton8_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Master")
Dim nextrow As Long
'find first empty row in database
With Worksheets("Master")
nextrow = .Range("p65536").End(xlUp).Row + 1
End With
'check to see if there's room
If nextrow >= 6589 Then
MsgBox "out of room!"
Exit Sub
End If
With Worksheets("Master")
.Cells(nextrow, 1).Value = InquiryDate
.Cells(nextrow, 2).Value = DatabaseAccessed
.Cells(nextrow, 3).Value = Requestor
.Cells(nextrow, 4).Value = AccountAccessed
.Cells(nextrow, 5).Value = RequestReason
.Cells(nextrow, 6).Value = InformationRequested
.Cells(nextrow, 7).Value = Comments
End With
End Sub