Entry Form Problem

[Hazel]

Hi There

I'm having a problem with a race entry form (please don't ask) and I'm using
a UserForm to process and enter the details on to a sheet named "Channel"
each competitor can make 3 entries and each one goes on the row immeadiately
after their first entry using the following code.

Private Sub Cmd506_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Channel")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row



'copy the data to the database

ws.Cells(iRow, 1).Value = " " & Trim(UserForm2.TB511.Value) + " " &
Trim(UserForm2.TB510.Value)
ws.Cells(iRow, 2).Value = Me.Club1.Value
ws.Cells(iRow, 3).Value = Me.Tb507.Value
ws.Cells(iRow, 4).Value = Me.Tb500.Value * 5#
ws.Cells(iRow, 5).Value = Me.Tb501.Value * 0.5
ws.Cells(iRow, 6).Value = Me.Tb502.Value * 1#
ws.Cells(iRow, 7).Value = Me.Tb503.Value * 2#
ws.Cells(iRow, 8).Value = Me.Tb504.Value * 5#
ws.Cells(iRow, 9).Value = Me.Tb505.Value * 1#
ws.Cells(iRow, 10).Value = Me.Tb506.Value * 2#
Me.Tb519.Value = Format(Val(Trim(Tb500.Value * 5#))) + (Val(Trim(Tb501.Value
* 0.5))) + (Val(Trim(Tb502.Value * 1#))) + (Val(Trim(Tb503.Value * 2#))) +
(Val(Trim(Tb504.Value * 5#))) + (Val(Trim(Tb505.Value * 1#))) +
(Val(Trim(Tb506.Value * 2#)))
ws.Cells(iRow, 11).Value = Me.Tb519.Value


Me.Tb500.Value = ""
Me.Tb501.Value = ""
Me.Tb502.Value = ""
Me.Tb503.Value = ""
Me.Tb504.Value = ""
Me.Tb505.Value = ""
Me.Tb506.Value = ""
Me.Tb507.Value = ""

End Sub

Everything enters on the sheet OK but I have to keep flicking between the
UserForm and the sheet to see if everything is correct and in Column L I
have the usual formula =sum(k2:K4) to finally check the grand total of the 3
entries. And of course I have to enter this formula for the next competitor
=sum(k5:k7) and so on down the sheet and with normally over 100 competitors
usually taking part its a real pain in the rear end -- get my meaning folks.
Could really do without that formula on the sheet ( deletes are so damned
easy) can the UserForm not do something similar??

 

[Tom Ogilvy]

Dim rFirst as Long, rLast as Long
Dim i as Long
' assumes first row of data is row 2
' change to suit
rFirst = 2
rLast = Cells(rFirst,"K").End(xldown)
for i = rFirst to rLast step 3
cells(i,"L").Value = Application.Sum( _
cells(i,"K").Resize(3,1))
Next

 

[Hazel]

Hi Tom

Thanks for quick response, have entered the code and even tried adding
another Command Button it bugs out on this line

rLast = Cells(rFirst, "K").End(xlDown) '<<<< rLast = 0

even though I have entered the members info

Any suggestions please and am I entering the code in the correct place ie
the Userform code not in a Module

 

[Tom Ogilvy]

Dim rFirst as Long, rLast as Long
Dim i as Long
' assumes first row of data is row 2
' change to suit
rFirst = 2
rLast = Cells(rFirst,"K").End(xldown).row
for i = rFirst to rLast step 3
cells(i,"L").Value = Application.Sum( _
cells(i,"K").Resize(3,1))
Next