Userform data entery

proctk

Well-known Member
Joined
Dec 24, 2004
Messages
840
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]"
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
You could always try this change to your code.

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

HTH
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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(...
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top