MrExcel Publishing
Your One Stop for Excel Tips & Solutions

help with databases


Posted by Josh Avila on December 13, 2001 9:55 AM

i've created a simple data entry form in excel using VBA and as before, i have a few questions. currently, when a "submitt" button is pressed on the form, all the data from the fields is copied to a line database. of course if i want to enter a new line of data, it writes over the existing line. what i would like to know, is there a small bit of code or could someone point me to a helpfull ref so that once the "submitt" button is pushed, the data is copied and the next cell to receive data is the row below the last entered. i figure i'd use Select.Cell or something along those lines...

thanks a ton guys...


Posted by Russell Hauf on December 13, 2001 10:21 AM

I would do something like this:


Option Explicit

Sub TransferData()

Dim intNewRow As Integer

' This first line gives you the last cell in Column A
' that has data in it.
Range("A65536").End(xlUp).Select
' Now you need to move down one row - several ways to do
' this - here are 2:
intNewRow = ActiveCell.Row + 1
' Paste your data into Cells(intNewRow, 1), etc.
' Or, you can do something like:
ActiveCell.Offset(1, 0).Select
' Paste your data to the ActiveCell


' Hope this gives you some ideas,
' Russell

End Sub