How do I select the first empty row in row "K". I want to use a form to copy
the following info to Row 2, Column "K".

Form Info:
Auto Year
Auto Make
Auto Model
Auto VIN

Worksheet Layout:
Col J Col K Col L Col M
Row 1 Year Make Model VIN
Row 2 2000 Toyota UBY xxxxxxxxx
Row 3 2000 Toyota UBY xxxxxxxxx

Please let me know.

I am currently using the following which ends up writing in Cols A-D, Row 2

Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("Lien Registration")
Dim nextrow As Long

'find first empty row in database
With Worksheets("Lien Registration")
nextrow = .Range("k65536").End(xlUp).Row + 1
End With
'always start with the even numbered row
If nextrow Mod 2 = 1 Then
nextrow = nextrow + 1
End If
'check to see if there's room
If nextrow = 500 Then
MsgBox "out of room!"
Exit Sub
End If
With Worksheets("Lien Registration")
.Cells(nextrow, 1).Value = MVYear
.Cells(nextrow, 2).Value = MVMake
.Cells(nextrow, 3).Value = MVModel
.Cells(nextrow, 4).Value = MVVin
End With

Hi,
The problem in you code is that you are telling it to copy in column A to D
change last part for this


..Cells(nextrow, 11).Value = MVYear
.Cells(nextrow,12).Value = MVMake
.Cells(nextrow, 13).Value = MVModel
.Cells(nextrow, 14).Value = MVVin

The above will copy starting in column K to M


"Tdungate" wrote:

How do I select the first empty row in row "K". I want to use a form to copy
the following info to Row 2, Column "K".

Form Info:
Auto Year
Auto Make
Auto Model
Auto VIN

Worksheet Layout:
Col J Col K Col L Col M
Row 1 Year Make Model VIN
Row 2 2000 Toyota UBY xxxxxxxxx
Row 3 2000 Toyota UBY xxxxxxxxx

Please let me know.

I am currently using the following which ends up writing in Cols A-D, Row 2

Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("Lien Registration")
Dim nextrow As Long

'find first empty row in database
With Worksheets("Lien Registration")
nextrow = .Range("k65536").End(xlUp).Row + 1
End With
'always start with the even numbered row
If nextrow Mod 2 = 1 Then
nextrow = nextrow + 1
End If
'check to see if there's room
If nextrow = 500 Then
MsgBox "out of room!"
Exit Sub
End If
With Worksheets("Lien Registration")
.Cells(nextrow, 1).Value = MVYear
.Cells(nextrow, 2).Value = MVMake
.Cells(nextrow, 3).Value = MVModel
.Cells(nextrow, 4).Value = MVVin
End With

Use
Dim lasRow as Long
With ActiveSheet
lastRow = .Cells(.Rows.Count, "K").End(xlUp).Row + 1
End With

"Tdungate" wrote:

How do I select the first empty row in row "K". I want to use a form to copy
the following info to Row 2, Column "K".

Form Info:
Auto Year
Auto Make
Auto Model
Auto VIN

Worksheet Layout:
Col J Col K Col L Col M
Row 1 Year Make Model VIN
Row 2 2000 Toyota UBY xxxxxxxxx
Row 3 2000 Toyota UBY xxxxxxxxx

Please let me know.

I am currently using the following which ends up writing in Cols A-D, Row 2

Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("Lien Registration")
Dim nextrow As Long

'find first empty row in database
With Worksheets("Lien Registration")
nextrow = .Range("k65536").End(xlUp).Row + 1
End With
'always start with the even numbered row
If nextrow Mod 2 = 1 Then
nextrow = nextrow + 1
End If
'check to see if there's room
If nextrow = 500 Then
MsgBox "out of room!"
Exit Sub
End If
With Worksheets("Lien Registration")
.Cells(nextrow, 1).Value = MVYear
.Cells(nextrow, 2).Value = MVMake
.Cells(nextrow, 3).Value = MVModel
.Cells(nextrow, 4).Value = MVVin
End With

This portion of your code writes the values to the worksheet:

With Worksheets("Lien Registration")
.Cells(nextrow, 1).Value = MVYear
.Cells(nextrow, 2).Value = MVMake
.Cells(nextrow, 3).Value = MVModel
.Cells(nextrow, 4).Value = MVVin
End With

Using .cells() allows you to specify the column as a number or a string.

So you can use:

With Worksheets("Lien Registration")
.Cells(nextrow, "K").Value = MVYear
.Cells(nextrow, "L").Value = MVMake
.Cells(nextrow, "M").Value = MVModel
.Cells(nextrow, "N").Value = MVVin
End With

or

With Worksheets("Lien Registration")
.Cells(nextrow, 11).Value = MVYear
.Cells(nextrow, 12).Value = MVMake
.Cells(nextrow, 13).Value = MVModel
.Cells(nextrow, 14).Value = MVVin
End With

Tdungate wrote:

How do I select the first empty row in row "K". I want to use a form to copy
the following info to Row 2, Column "K".

Form Info:
Auto Year
Auto Make
Auto Model
Auto VIN

Worksheet Layout:
Col J Col K Col L Col M
Row 1 Year Make Model VIN
Row 2 2000 Toyota UBY xxxxxxxxx
Row 3 2000 Toyota UBY xxxxxxxxx

Please let me know.

I am currently using the following which ends up writing in Cols A-D, Row 2

Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("Lien Registration")
Dim nextrow As Long

'find first empty row in database
With Worksheets("Lien Registration")
nextrow = .Range("k65536").End(xlUp).Row + 1
End With
'always start with the even numbered row
If nextrow Mod 2 = 1 Then
nextrow = nextrow + 1
End If
'check to see if there's room
If nextrow = 500 Then
MsgBox "out of room!"
Exit Sub
End If
With Worksheets("Lien Registration")
.Cells(nextrow, 1).Value = MVYear
.Cells(nextrow, 2).Value = MVMake
.Cells(nextrow, 3).Value = MVModel
.Cells(nextrow, 4).Value = MVVin
End With

--

Dave Peterson

I am currently using a combination of your suggestion and Eduardos which is
giving me the same 1004 error result:

Private Sub CommandButton11_Click()
Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("Lien Registration")
Dim nextrow As Long

'find first empty row in database
With Worksheets("Lien Registration")
'nextrow = .Range("k").End(xlUp).Row + 1
lastRow = .Cells(.Rows.Count, "K").End(xlUp).Row + 1
End With
'always start with the even numbered row
If nextrow Mod 2 = 1 Then
nextrow = nextrow + 1
End If
'check to see if there's room
If nextrow = 500 Then
MsgBox "out of room!"
Exit Sub
End If
With Worksheets("Lien Registration")
..Cells(nextrow, "K").Value = Me.MVYear.Value
..Cells(nextrow, "L").Value = Me.MVMake.Value
..Cells(nextrow, "M").Value = Me.MVModel.Value
..Cells(nextrow, "N").Value = Me.MVVin.Value
End With
End Sub



"Dave Peterson" wrote:

This portion of your code writes the values to the worksheet:

With Worksheets("Lien Registration")
.Cells(nextrow, 1).Value = MVYear
.Cells(nextrow, 2).Value = MVMake
.Cells(nextrow, 3).Value = MVModel
.Cells(nextrow, 4).Value = MVVin
End With

Using .cells() allows you to specify the column as a number or a string.

So you can use:

With Worksheets("Lien Registration")
.Cells(nextrow, "K").Value = MVYear
.Cells(nextrow, "L").Value = MVMake
.Cells(nextrow, "M").Value = MVModel
.Cells(nextrow, "N").Value = MVVin
End With

or

With Worksheets("Lien Registration")
.Cells(nextrow, 11).Value = MVYear
.Cells(nextrow, 12).Value = MVMake
.Cells(nextrow, 13).Value = MVModel
.Cells(nextrow, 14).Value = MVVin
End With

Tdungate wrote:

How do I select the first empty row in row "K". I want to use a form to copy
the following info to Row 2, Column "K".

Form Info:
Auto Year
Auto Make
Auto Model
Auto VIN

Worksheet Layout:
Col J Col K Col L Col M
Row 1 Year Make Model VIN
Row 2 2000 Toyota UBY xxxxxxxxx
Row 3 2000 Toyota UBY xxxxxxxxx

Please let me know.

I am currently using the following which ends up writing in Cols A-D, Row 2

Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("Lien Registration")
Dim nextrow As Long

'find first empty row in database
With Worksheets("Lien Registration")
nextrow = .Range("k65536").End(xlUp).Row + 1
End With
'always start with the even numbered row
If nextrow Mod 2 = 1 Then
nextrow = nextrow + 1
End If
'check to see if there's room
If nextrow = 500 Then
MsgBox "out of room!"
Exit Sub
End If
With Worksheets("Lien Registration")
.Cells(nextrow, 1).Value = MVYear
.Cells(nextrow, 2).Value = MVMake
.Cells(nextrow, 3).Value = MVModel
.Cells(nextrow, 4).Value = MVVin
End With

--

Dave Peterson

Hi you are now using lastrow to calculate row number (not Dimmed)
but still using nextrow (which will be 0) when trying the write the data.

--
Regards
Roger Govier

"Tdungate" wrote in message
...
I am currently using a combination of your suggestion and Eduardos which
is
giving me the same 1004 error result:

Private Sub CommandButton11_Click()
Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("Lien Registration")
Dim nextrow As Long

'find first empty row in database
With Worksheets("Lien Registration")
'nextrow = .Range("k").End(xlUp).Row + 1
lastRow = .Cells(.Rows.Count, "K").End(xlUp).Row + 1
End With
'always start with the even numbered row
If nextrow Mod 2 = 1 Then
nextrow = nextrow + 1
End If
'check to see if there's room
If nextrow = 500 Then
MsgBox "out of room!"
Exit Sub
End If
With Worksheets("Lien Registration")
.Cells(nextrow, "K").Value = Me.MVYear.Value
.Cells(nextrow, "L").Value = Me.MVMake.Value
.Cells(nextrow, "M").Value = Me.MVModel.Value
.Cells(nextrow, "N").Value = Me.MVVin.Value
End With
End Sub



"Dave Peterson" wrote:

This portion of your code writes the values to the worksheet:

With Worksheets("Lien Registration")
.Cells(nextrow, 1).Value = MVYear
.Cells(nextrow, 2).Value = MVMake
.Cells(nextrow, 3).Value = MVModel
.Cells(nextrow, 4).Value = MVVin
End With

Using .cells() allows you to specify the column as a number or a string.

So you can use:

With Worksheets("Lien Registration")
.Cells(nextrow, "K").Value = MVYear
.Cells(nextrow, "L").Value = MVMake
.Cells(nextrow, "M").Value = MVModel
.Cells(nextrow, "N").Value = MVVin
End With

or

With Worksheets("Lien Registration")
.Cells(nextrow, 11).Value = MVYear
.Cells(nextrow, 12).Value = MVMake
.Cells(nextrow, 13).Value = MVModel
.Cells(nextrow, 14).Value = MVVin
End With

Tdungate wrote:

How do I select the first empty row in row "K". I want to use a form to
copy
the following info to Row 2, Column "K".

Form Info:
Auto Year
Auto Make
Auto Model
Auto VIN

Worksheet Layout:
Col J Col K Col L Col M
Row 1 Year Make Model VIN
Row 2 2000 Toyota UBY xxxxxxxxx
Row 3 2000 Toyota UBY xxxxxxxxx

Please let me know.

I am currently using the following which ends up writing in Cols A-D,
Row 2

Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("Lien Registration")
Dim nextrow As Long

'find first empty row in database
With Worksheets("Lien Registration")
nextrow = .Range("k65536").End(xlUp).Row + 1
End With
'always start with the even numbered row
If nextrow Mod 2 = 1 Then
nextrow = nextrow + 1
End If
'check to see if there's room
If nextrow = 500 Then
MsgBox "out of room!"
Exit Sub
End If
With Worksheets("Lien Registration")
.Cells(nextrow, 1).Value = MVYear
.Cells(nextrow, 2).Value = MVMake
.Cells(nextrow, 3).Value = MVModel
.Cells(nextrow, 4).Value = MVVin
End With

--

Dave Peterson

I managed to get the following to work for me. Thanks you all for your
assistance.

"Tdungate" wrote:

I am currently using a combination of your suggestion and Eduardos which is
giving me the same 1004 error result:

Private Sub CommandButton11_Click()
Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("Lien Registration")
Dim nextrow As Long

'find first empty row in database
With Worksheets("Lien Registration")
'nextrow = .Range("k").End(xlUp).Row + 1
lastRow = .Cells(.Rows.Count, "K").End(xlUp).Row + 1
End With
'always start with the even numbered row
If nextrow Mod 2 = 1 Then
nextrow = nextrow + 1
End If
'check to see if there's room
If nextrow = 500 Then
MsgBox "out of room!"
Exit Sub
End If
With Worksheets("Lien Registration")
.Cells(nextrow, "K").Value = Me.MVYear.Value
.Cells(nextrow, "L").Value = Me.MVMake.Value
.Cells(nextrow, "M").Value = Me.MVModel.Value
.Cells(nextrow, "N").Value = Me.MVVin.Value
End With
End Sub



"Dave Peterson" wrote:

This portion of your code writes the values to the worksheet:

With Worksheets("Lien Registration")
.Cells(nextrow, 1).Value = MVYear
.Cells(nextrow, 2).Value = MVMake
.Cells(nextrow, 3).Value = MVModel
.Cells(nextrow, 4).Value = MVVin
End With

Using .cells() allows you to specify the column as a number or a string.

So you can use:

With Worksheets("Lien Registration")
.Cells(nextrow, "K").Value = MVYear
.Cells(nextrow, "L").Value = MVMake
.Cells(nextrow, "M").Value = MVModel
.Cells(nextrow, "N").Value = MVVin
End With

or

With Worksheets("Lien Registration")
.Cells(nextrow, 11).Value = MVYear
.Cells(nextrow, 12).Value = MVMake
.Cells(nextrow, 13).Value = MVModel
.Cells(nextrow, 14).Value = MVVin
End With

Tdungate wrote:

How do I select the first empty row in row "K". I want to use a form to copy
the following info to Row 2, Column "K".

Form Info:
Auto Year
Auto Make
Auto Model
Auto VIN

Worksheet Layout:
Col J Col K Col L Col M
Row 1 Year Make Model VIN
Row 2 2000 Toyota UBY xxxxxxxxx
Row 3 2000 Toyota UBY xxxxxxxxx

Please let me know.

I am currently using the following which ends up writing in Cols A-D, Row 2

Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("Lien Registration")
Dim nextrow As Long

'find first empty row in database
With Worksheets("Lien Registration")
nextrow = .Range("k65536").End(xlUp).Row + 1
End With
'always start with the even numbered row
If nextrow Mod 2 = 1 Then
nextrow = nextrow + 1
End If
'check to see if there's room
If nextrow = 500 Then
MsgBox "out of room!"
Exit Sub
End If
With Worksheets("Lien Registration")
.Cells(nextrow, 1).Value = MVYear
.Cells(nextrow, 2).Value = MVMake
.Cells(nextrow, 3).Value = MVModel
.Cells(nextrow, 4).Value = MVVin
End With

--

Dave Peterson

Hi

You are writing to the wrong column numbers
With Worksheets("Lien Registration")
.Cells(nextrow, 10).Value = MVYear
.Cells(nextrow, 11).Value = MVMake
.Cells(nextrow, 12).Value = MVModel
.Cells(nextrow, 13).Value = MVVin
End With

will write to columns J:M
--
Regards
Roger Govier

"Tdungate" wrote in message
...
How do I select the first empty row in row "K". I want to use a form to
copy
the following info to Row 2, Column "K".

Form Info:
Auto Year
Auto Make
Auto Model
Auto VIN

Worksheet Layout:
Col J Col K Col L Col M
Row 1 Year Make Model VIN
Row 2 2000 Toyota UBY xxxxxxxxx
Row 3 2000 Toyota UBY xxxxxxxxx

Please let me know.

I am currently using the following which ends up writing in Cols A-D, Row
2

Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("Lien Registration")
Dim nextrow As Long

'find first empty row in database
With Worksheets("Lien Registration")
nextrow = .Range("k65536").End(xlUp).Row + 1
End With
'always start with the even numbered row
If nextrow Mod 2 = 1 Then
nextrow = nextrow + 1
End If
'check to see if there's room
If nextrow = 500 Then
MsgBox "out of room!"
Exit Sub
End If
With Worksheets("Lien Registration")
.Cells(nextrow, 1).Value = MVYear
.Cells(nextrow, 2).Value = MVMake
.Cells(nextrow, 3).Value = MVModel
.Cells(nextrow, 4).Value = MVVin
End With