Move to the next availble empty cell

[vaughn9]

I have created some text boxes to enter information. i want the information to go to excel when I click my command button. This is my code
'info placed in General
Dim oExcel As New Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim Surname, Firstname, Pa, DOB, Tel As String

Private Sub Command1_Click()
Set oBook = oExcel.Workbooks.Add()
Set oSheet = oExcel.ActiveSheet
Rem makes Excel visible
oExcel.Visible = True
'specify value of textboxes
Surname = Text1
Firstname = Text2
Pa = parish
DOB = Text3
Tel = Text4

Rem fill in row values
oSheet.Cells(1, 1).Value = "SURNAME"
oSheet.Cells(1, 2).Value = "FIRSTNAME"
oSheet.Cells(1, 3).Value = "PARISH"
oSheet.Cells(1, 4).Value = "DOB"
oSheet.Cells(1, 5).Value = "Tel. No"

oSheet.Cells(2, 1).Value = Surname
oSheet.Cells(2, 2).Value = Firstname
oSheet.Cells(2, 3).Value = Pa
oSheet.Cells(2, 4).Value = DOB
oSheet.Cells(2, 5).Value = Tel

End Sub

My problem is that when I add a second record the first overwrites the second. I need to go to the next availble empty cell when I enter the second record so that the second persons information would begin in row3 column1, and the next in row4, column1. I was thinking if I say move down 1 and move across to the first cell on the left that might work but I am not too sure. I need my next record to go to the next available row.

Help

 

[Foada]

I am not sure if I understand what you want but you could do this:

Dim bChk as Boolean
Dim iR As Long

bChk = True
iR = 1
Do While bChk
If IsEmpty(oSheet.Cells(iR,1) Then
bChk = False
Else
iR = iR + 1
End If
DoEvents
Loop

Just be careful with the loop as it will hog processor time.

Anything is possible, the problem is I only have one lifetime.

 

[vaughn9]

If IsEmpty(oSheet.Cells(iR,1) Then

this line is giving me a syntax error

To explain more clearly what I need to happen. I need to output textbox information to an excel row. My code is doing that. However when I enter another set of info into the text boxes it overwrites the last set of info. I need to be able to enter data in the textboxes for a person, output that data to excel, enter data for the second person and so on and output that data in a row below the first row.

I need code that will allow me to drop a row after the first row and place the next set of info there

 

[MiggyD]

It appears that this is the first lanuage you are studying. You need to check your parentheses.
--MiggyD

Never be afraid to try something new. Remember that amateurs built the Ark. Professionals built the Titanic.

 

[vaughn9]

this worked.

range("A65536&quot.end(xlup).offset(1,0).select

could you be more specific with your parenthesis comment?

 

[Foada]

The parenthesis comment was with regards to this
If IsEmpty(oSheet.Cells(iR,1) Then
should be
If IsEmpty(oSheet.Cells(iR,1)) Then

The code I gave you was not to be places in the cell itself it is to be placed in the work sheet code as a macro. I did not think you were trying to use the code in a cell formula. I still am not sure if I understand your question completely. Do you have 5 textboxes on the worksheet as the data entry point and then what to fill in the entered data from those textboxes into a database type worksheet?? Anything is possible, the problem is I only have one lifetime.

 

[vaughn9]

I have 5 textboxes on a vb form. my code will output the information entered into the textboxes in an excel worksheet in a1,b1,c1 etc. I probably need to put a loop so that the form will continue to appear so I could enter data for a number of people. Then I wanted to be able to click on my command button and which if it had the correct code would place each record below the other in the excelwork sheet. Which is why I needed code to go to the next empty cell in the worksheet.

 

[Foada]

Are you using excel as an OLE container within your form that has the textboxes or does the excel worksheet just operate in the background as a database? Anything is possible, the problem is I only have one lifetime.

 

[vaughn9]

excel is operating in the background as a database