Userform data entery

proctk

Well-known Member
Joined
Dec 24, 2004
Messages
839
Hi

Below is code attached to a command button on a userform. The userform is used to load data to the worksheet. The data to be entered needs to start on row 4.

The problem I'm running into is when the second entery is keyed is its not moving to the next row, it's overwriting the fist entery

any thoughts why this is happening

Code:
Private Sub CommandButton1_Click()
Dim iRow As Long
 
   Sheets("BranchTracking").Activate
        iRow = Sheets("BranchTracking").Cells(65000, 4).End(xlUp).Offset(1, 0).Row
        
        Cells(iRow, 1).Value = Me.Day.Value
        Cells(iRow, 2).Value = Me.Emp.Value
        Cells(iRow, 3).Value = Me.Activity.Value
        Cells(iRow, 4).Value = Me.Pissued.Value
        Cells(iRow, 5).Value = Me.TOut.Value
        Cells(iRow, 6).Value = Me.TIn.Value
        Cells(iRow, 7).FormulaR1C1 = "=RC[-1]-RC[-2]-RC[-3]"
 

StevenD

Active Member
Joined
Nov 17, 2004
Messages
262
You could always try this change to your code.

Code:
    iRow = Sheets("BranchTracking").Cells(65000, 4).End(xlUp).Row+1
HTH
 

proctk

Well-known Member
Joined
Dec 24, 2004
Messages
839
Thanks for the tip, Its did not help. Still struggling with this one
 

rs2k

Well-known Member
Joined
Aug 15, 2004
Messages
1,413
Heres some code that I use (and it works) amend the ranges to suit

Private Sub cmdAddtoList_Click()
Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
'find first row in database
irow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
With ws
.Range("B1") = cmbSites.Value
.Range("A" & irow) = txtBuilding.Value
.Range("B" & irow) = txtFloor.Text
.Range("C" & irow) = txtRoom.Text
.Range("D" & irow) = txtxxx.Value
.Range("E" & irow) = txtyyy.Value
.Range("F" & irow) = chkA4bw.Value
.Range("G" & irow) = chkA4Col.Value
.Range("H" & irow) = chkA3Col.Value
.Range("I" & irow) = optBase.Value
.Range("J" & irow) = optHeightened.Value
txtBuilding = " "
txtFloor = " "
txtRoom = " "
txtxxx = " "
txtyyy = " "
chkA4bw = False
chkA4Col = False
chkA3Col = False
optBase = True
optHeightened = False
End With
End Sub

Cheers
Colin.
 

proctk

Well-known Member
Joined
Dec 24, 2004
Messages
839
Thank you for the posy, for some reason its still not working there must be some issue with the sheet that I cannot figure out.

I deleted the sheet and created a new one to see it that would help but still same thing
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
9,999
Try setting a watch to Sheets("BranchTracking").Cells(65000, 4).End(xlUp).Offset(1, 0).Address and a breakpoint on Sheets("BranchTracking").Activate and step through. Are you getting the value you expect?
 

proctk

Well-known Member
Joined
Dec 24, 2004
Messages
839
I figured out why it is/was not working. If I don't put a value in every text boxe on the userform then it does not work. If I put a value in all textboxes on the userform then send the data to the worksheet it works

any thoughts on why this is
 

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
proctk
... Your code is telling the macro to determine the next row to use by looking at last row used in Column 4.

Here's a demo of your method and the method I prefer. :wink:

Code:
Public Sub demo()

    ' YOUR METHOD
    With Sheets("BranchTracking")
        ' look at colun 4 to determine next row
        iRow = .Cells(65000, 4).End(xlUp).Offset(1, 0).Row
        MsgBox iRow
    End With
    
    
    ' MY METHOD
    With Sheets("BranchTracking")
        ' look at column 1 to 8 to determine next row
        iRow = .Range("A1:H65536").Find("*", SearchDirection:=xlPrevious).Row + 1
        MsgBox iRow
    End With

End Sub

Code:
Private Sub CommandButton1_Click()
Dim iRow As Long

With Sheets("BranchTracking")
    .Activate
    iRow = .Range("A1:H65536").Find("*", SearchDirection:=xlPrevious).Row + 1
            
    .Cells(iRow, 1).Value = Me.Day.Value
    .Cells(iRow, 2).Value = Me.Emp.Value
    .Cells(iRow, 3).Value = Me.Activity.Value
    .Cells(iRow, 4).Value = Me.Pissued.Value
    .Cells(iRow, 5).Value = Me.TOut.Value
    .Cells(iRow, 6).Value = Me.TIn.Value
    .Cells(iRow, 7).FormulaR1C1 = "=RC[-1]-RC[-2]-RC[-3]"
End With
End Sub
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
9,999
Hi Nimrod,

Excellent suggestion on using .FIND("*") over .END(xlUP). If I may, could I add?
  • You might want to add the SearchOrder arg to the FIND function, setting to SearchOrder:=xlByRows otherwise Excel will use the last search order, and if that is set to xlByColumns it's the same as xlUP on the righthand column.
  • Given what we've learned about XL12, I'm trying to get into the habit of coding like iRow = .Range("A1:H" & .Rows.Count).Find(...
 

Forum statistics

Threads
1,078,246
Messages
5,339,064
Members
399,276
Latest member
Donjayok

Some videos you may like

This Week's Hot Topics

Top