dumping contents of UserForm into next avail row

[draconus]

This is silly I know, I have looked around various resources but I can not find what i need to do to dump the contents of a Userform into the next available row in a spreadsheet. I would have thought this was a basic function, but I am obviously looking in the wrong places. Please help with a statement or pointer to a resource.
TIA
Mal 'going-grey-quickly' Thomas

 

[Andrew Poulsom]

In VBA:

Range("A65536").End(xlUp).Offset(1,0)

returns the next empty cell in column A.

 

[Richie(UK)]

Hi Mal,

As regards finding the next available row, try this routine from Dave Hawley's site for finding the last row, then offset by one:

<pre>Sub FindLastRow()
Dim LastRow As Long
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
MsgBox LastRow
End If
End Sub</pre>

With regards to 'dumping' info from a Userform, I assume that you mean values from textboxes. Try something like this in the CommandButton1_Click code:
ThisWorkbook.Sheets(1).Range("A1").Value = TextBox1.Value

Its probably best to find your next available row first and then loop through your textboxes incrementing the row destination with each new textbox.

HTH

 

[draconus]

magic ! all good tips, thanks very much, folks. I now think I am on the correct path to glory !

Cheers
Mal
Hong Kong

 

[ElectricSkywalker]

I am in the same boat as you...I have been creating a userform in Excel...and then placing the data into a Excel Spreadsheet...so I can do lookups on it.

This is the code I have been using:


Private Sub bnOK_Click()
'Open the Storage Sheet for the Raw Data
Sheets("Storage").Activate
'Find the Next Row
NextRow = Application.WorksheetFunction.CountA(Range("A:A")) + 1
'Add the entered data to the next avaliable line
Cells(NextRow, 1) = txtFirstName.Text
Cells(NextRow, 2) = txtSurname.Text
Cells(NextRow, 3) = cbDeptarment.Value
Cells(NextRow, 4) = cbManager.Value
Cells(NextRow, 5) = lbSunday.Value

' Add in the Code
Cells(NextRow, 11) = "SARS"

Cells(NextRow, 12) = txtSARS1.Text
Cells(NextRow, 13) = txtSARS2.Text
Cells(NextRow, 14) = txtSARS3.Text
Cells(NextRow, 15) = txtSARS4.Text
Cells(NextRow, 16) = txtSARS5.Text


Cells(NextRow, 42) = txtSARSCOMMENT.Text



'Clear all the Entries Back to Blank
txtFirstName.Text = ""
txtSurname.Text = ""
cbDeptarment.Value = ""
cbManager.Value = ""
lbSunday.Value = ""
txtSARS1.Text = ""
txtSARS2.Text = ""
txtSARS3.Text = ""
txtSARS4.Text = ""
txtSARS5.Text = ""

txtSARSCOMMENT.Text = ""

'Set the data entry focus back to the first entry
txtFirstName.SetFocus

End Sub


Private Sub bnCancel_Click()

Unload Me
End Sub

 

[draconus]

Thanks for all the help. I have now got a beautiful looking data entry page. My operators now love me !

Cheers
Mal
Hong Kong