How Do I Offset Where Excel Userform Inserts Data? RRS feed

  • Question

  • Hi,


    How do I tell my userform to find the first available cell from a specific row and column?

    I am completely new to Excel programming and I am learning how to create basic userforms to input data to a spreadsheet.

    I have a basic spreadsheet that records 7 columns of data over an unlimited number of rows, usually only up to 50 maximum.

    With some headers, a border and so on, my first blank row is row 5 and starts in column B. Therefore the first cell of my 7 column sheet is at B:5.

    When I use my userform with a text label and text box for each field on the sheet, the submit command button always finds A:13 on my sheet as the first available cell.

    I think this is because I have some reference text to the right of the main data area down to row 12. This is necessary at the moment to enable some specific functionality within the sheet itself for collating data, etc.

    All I want is to have my form post the data from column B and not A. Also the use rows from 5 down, ignoring any other text or data in cells outside my main data area.

    I have been learning from a basic tutorial, which may look familiar to you all...

    Thank you,



    Userform code is:

    Private Sub cmdAdd_Click()

    Dim iRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("January")

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

    'check for a date
    If Trim(Me.txtDate.Value) = "" Then
      MsgBox "Please enter a date"
      Exit Sub
    End If

    'copy the data to the database
    ws.Cells(iRow, 1).Value = Me.txtDate.Value
    ws.Cells(iRow, 2).Value = Me.txtTime.Value
    ws.Cells(iRow, 3).Value = Me.txtLoc.Value
    ws.Cells(iRow, 4).Value = Me.txtOffence.Value
    ws.Cells(iRow, 5).Value = Me.txtName.Value
    ws.Cells(iRow, 6).Value = Me.txtRef.Value
    ws.Cells(iRow, 7).Value = Me.txtComments.Value

    'clear the data
    Me.txtDate.Value = ""
    Me.txtTime.Value = ""
    Me.txtLoc.Value = ""
    Me.txtOffence.Value = ""
    Me.txtName.Value = ""
    Me.txtRef.Value = ""
    Me.txtComments.Value = ""

    End Sub

    Saturday, August 4, 2012 1:10 PM

All replies

  • Change the lines

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


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

    This will make the code look at columns B:H only to find the first available row. And if you want to start copying in column B instead of column A, increase each of the column numbers by 1 in the lines that copy the data. So:

        'copy the data to the database
         ws.Cells(iRow, 2).Value = Me.txtDate.Value
         ws.Cells(iRow, 3).Value = Me.txtTime.Value

    Regards, Hans Vogelaar

    Saturday, August 4, 2012 2:31 PM
  • Alternatively use:

    Dim Rng As Range
    Set Rng = Worksheets("January").Range("B5")
    If Not IsEmpty(Rng.Offset(1, 0)) Then 'If not first row of data
    Set Rng = Rng.End(xlDown).Offset(1, 0) 'Point to first blank row
    End If
    Rng.Range("A1").Value = Me.txtDate.Value
    Rng.Range("B1").Value = Me.txtTime.Value
    Rng.Range("C1").Value = Me.txtLoc.Value
    Rng.Range("D1").Value = Me.txtOffence.Value
    Rng.Range("E1").Value = Me.txtName.Value
    Rng.Range("F1").Value = Me.txtRef.Value
    Rng.Range("G1").Value = Me.txtComments.Value

    Type ?Rng.Range("A1").address
    in the immediate window (Ctrl+G displays it) to see he address of the addressed cell.

    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Sunday, August 5, 2012 2:12 AM
  • Hans and Rod,

    Thank you so much. I will try each of these to see what happens. I appreciate your time!


    Wednesday, August 8, 2012 8:55 AM