Userform data entery

[proctk]

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

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]

You could always try this change to your code.

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

HTH

 

[proctk]

Thanks for the tip, Its did not help. Still struggling with this one

 

[rs2k]

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]

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]

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]

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]

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:

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

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]

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(...

 

[Nimrod]

Hello Greg
Good tip ...thx :wink: