Solved: Form data to spreadsheet....

[Papadopoulos]

Just as everyone says... this should be simple.
I have managed to get the row number of the last row and increment it by 1 so that I can add new data via my userform...
Works fine for entering something like the date. When I want to pass the value of a text box to the cell in the next column.... I have my code out of whack. Big surprise.
Private Sub CommandButton2_Click()
Dim newRow As Range, newVers As Range
Dim SheetName As String
SheetName = "apphistory"
With Worksheets(SheetName)
MsgBox "First available row = " & (xlLastRow(SheetName) + 1), vbInformation, _
"Passed Sheet Name Demonstration"
Set newRow = .Cells((xlLastRow(SheetName) + 1), 1)
newRow.Value = Date
Set newVers = .Cells((xlLastRow(SheetName) + 1), 2) ' this should be unnecessary
newVers.Value = TextBox1 ' this should be unnecessary

' .Range.Cells((xlLastRow(SheetName) + 1), 2) = TextBox1
' .Range.Cells((xlLastRow(SheetName) + 1), 3) = TextBox2
' .Range.Cells((xlLastRow(SheetName) + 1), 4) = TextBox3
End With

End Sub I can get it to work if I define a range object (the two lines with trailing comments) but I don't think that is necessary. I want use my variable for the row number when I specify the range. The bottom 3 lines that are commented out were my attempt at this.

Thanks in advance.
:banghead:

[Trebor76]

Hi Papadopoulos,

Try the following - just add additional code for additional text boxes:


Private Sub CommandButton2_Click()

Dim strSheetPaste As String

strSheetPaste = "apphistory"

With Worksheets(strSheetPaste)
Range("A" & .Cells(Rows.Count, "A").End(xlUp).Row + 1).Value = Date
Range("B" & .Cells(Rows.Count, "B").End(xlUp).Row + 1).Value = TextBox1
Range("C" & .Cells(Rows.Count, "C").End(xlUp).Row + 1).Value = TextBox2
End With

End Sub

Regards,

Robert

[PhilC]

I think you might be having problem using the .Range.Cells(x,y) syntax if that is what you were using. I'm not sure what the
xlLastRow(SheetName) is as it does not show up as a valid keyword in Excel 2007. Try the following code and see if it works for you instead...

Dim newRow, newVers As Range
Dim SheetName As String
Dim LastRow As Integer

SheetName = "Sheet1"
LastRow = Worksheets(SheetName).UsedRange.Rows.Count

With Worksheets(SheetName)
MsgBox "first available row = " & (LastRow + 1), vbInformation, "Passed Sheet Name Demo"

Set newRow = .Cells((LastRow + 1), 1)

newRow.Value = Date

.Cells((LastRow + 1), 2) = TextBox1
.Cells((LastRow + 1), 3) = TextBox2
.Cells((LastRow + 1), 4) = TextBox3
End With

Cheers,
PhilC

[Papadopoulos]

Ok,

General apology.
I left out the function code.
Function xlLastRow(Optional WorksheetName As String) As Long

' find the last populated row in a worksheet

If WorksheetName = vbNullString Then WorksheetName = ActiveSheet.Name
With Worksheets(WorksheetName)
On Error Resume Next
xlLastRow = .Cells.Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByRows, xlPrevious).row
If Err <> 0 Then xlLastRow = 0
End With

End Function

Trebor76:
I've taken a quick look at what you are doing and it streamlines my efforts considerably. Only problem is that it doesn't enter the data in the sheet?
I don't understand why. It certainly does yield the right row.
The drawback to this method if I did get it to work though is that if anyone left a cell blank (yes, I should validate the form before writing the data back out to the spreadsheet) then the entries in cells A row, B row, C row etc might not all be in the same row.

PhilC:
You hit my problem on the head and again everything is nicely streamlined.
Only one problem. The first empty row should currently be Row 62 and for some reason you code finds it down around 133? There shouldn't be anything down there but if I do a ctrl end that is where it lands.
The function that I should have attached to begin with does in fact get me to the right place. I just wasn't able to use what it gave me for transferring my TextBox entries to my spreadsheet.
I am going to look at your example and see if I can straigten out my .Range.Cells(x,y) syntax.

Will post if I have success or whine for more help if I can't get it to behave.

Thanks!

[Papadopoulos]

This time you get the procedure and the function it calls.
Unfortunately it still doesn't work.
The .Range.Cells(x, y) syntax
It keeps giving me Run-time error '450':
wrong number of arguments or invalid property assignment.
.
.
.
Private Sub CommandButton2_Click()
Dim newRow As Range ' , newVers As Range
Dim SheetName As String
Dim lastRowNum As Integer
SheetName = "apphistory"
With Worksheets(SheetName)
MsgBox "First available row = " & (xlLastRow(SheetName) + 1), vbInformation, _
"Passed Sheet Name Demonstration" ' This is correct "62"


lastRowNum = (xlLastRow(SheetName) + 1) ' Just peachy
Set newRow = .Cells(lastRowNum, 1) ' Places date in "A62" Perfect!
newRow.Value = Date ' This also works

' .Range.Cells((xlLastRow(SheetName) + 1), 2) = TextBox1 ' This fails too
.Range.Cells(lastRowNum, 2) = TextBox1 ' This ought to work. Cell B62 based on my sample data
End With

End Sub
Function xlLastRow(Optional WorksheetName As String) As Long

' find the last populated row in a worksheet

If WorksheetName = vbNullString Then WorksheetName = ActiveSheet.Name
With Worksheets(WorksheetName)
On Error Resume Next
xlLastRow = .Cells.Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByRows, xlPrevious).row
If Err <> 0 Then xlLastRow = 0
End With

End Function
Tried one more thing as I posted;
I took out the .Range in the following line.
.Range.Cells(lastRowNum, 2) = TextBox1 Bingo!
Obviously I wound up with this change:
lastRowNum = (xlLastRow(SheetName) + 1)
as well.
I would expect that I would be able to use (xlLastRow(SheetName) + 1) in place of x in the right half of this in my .Range.Cells(x, y) designation but couldn't get it to work for the life of me.
Any ideas or was the concept flawed to begin with?

[Trebor76]

Hi Papadopoulos,

Here's my code revised with a method of finding the last row regardless of column it resides in. Note I tested this on a mocked up userform and it posted the contents of the text boxes correctly so I'm not sure why it's not doing the same for you :confused:


Private Sub CommandButton2_Click()

Dim strSheetPaste As String
Dim lngRowPaste As Long

strSheetPaste = "apphistory"

With Worksheets(strSheetPaste)

On Error Resume Next 'Ignore any errors from the following code.

'Find the last row by searching backward through all rows.
lngRowPaste = .Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row

If Err.Number <> 0 Then lngRowPaste = 0

On Error GoTo 0 'Nullify above error handler.

Range("A" & lngRowPaste + 1).Value = Date
Range("B" & lngRowPaste + 1).Value = TextBox1
Range("C" & lngRowPaste + 1).Value = TextBox2

End With

End Sub

Regards,

Robert

[PhilC]

Papadopoulos,

You had the right idea. I think the problem you experienced is because of the With block. I only see listings for a Range.Cells or a Worksheets.Cells function. I do not see a Worksheets.Range.Cells function. I thin when this syntax is used the range must be specified. so you would need to specify all of the cells on the worksheet. i.e. Worksheets.Range("A1:IV65536").Cells...

Because the commands are in the with block and you used the .Range preface you ended up with this scenario. I hope this helps explain a bit and I hope it makes sense.

:beerchug:

Phil C

[Papadopoulos]

Thanks for all the help....
Everyone solved makes the next easier to solve.... or at least I get farther in the hole before crying for help!
:help