inserting data in next empty row

[tazz]

Hello,

I created a form to introduce some data into a worksheet.

When I applied a formula in one cell(col D) I saw that the data skipped that row.

My guess is, the problem is the 3rd line in the code.I would like the code to look only for the firs 2 cell empty in that row instead of the entire row.

Bellow is the line I suspect is wrong.


Dim iRow As Long


Thank you for your help

 

[Luke M]

Hi tazz.

Nope, I don't think that's the only thing wrong. The line you posted simply says that the variable iRow will store a integer, not what it is. Could you post the whole macro, or at least the beginning portio up till where data is inserted? That would help us help you better.

 

[tazz]

Hello,

This is the code I use to insert data:

[pre]

Private Sub cmdIn_Click()
date_test = Now()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

'copy the data to the database
With ws
.Cells(iRow, 1).Value = Me.txtnumber.Value
.Cells(iRow, 2).Value = Now()
End With
'clear the data
Me.txtnumber.Value = ""
Me.txtnumber.SetFocus
End Sub

[/pre]

 

[Luke M]

Change this line:

[pre]

iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
to this:

iRow = ws.Range("A:B").Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

[/pre]
That way it will only look in the first two columns, rather than all columns.


PS. This forum is in the middle of migration. All posts made after 10:00 am EST on Friday will be lost after migration.