Problem with second count routine from userform

[Wendy]

Hi

When I add records through my form I need to add a counter to the cheque
number chq1, chq2 etc. My problem is other payment types are allowed and
should not be numbered. I already count the items entered as these are 1
less than the row number which works fine.

Thanks

Wendy

Private Sub CmdAdd_Click()
Dim lRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Data")
Dim Counter As Integer
Dim loopy As Integer
Dim ChqNo As Integer


'first empty row
lRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for Payment method entry
If Trim(Me.cboType.Value) = "" Then
Me.cboType.SetFocus
MsgBox "Please select a payment method"
Exit Sub
End If

Counter = lRow - 1

'copy the data to the spreadsheet
With ws
For loopy = 1 To lRow
.Cells(lRow, 1).Value = Counter 'Item No
.Cells(lRow, 3).Value = Me.TxtName.Value 'Payee Name
.Cells(lRow, 4).Value = Me.TxtFee.Value 'Amount

If Left(Cells(lRow, 5).Value, 3) = "CHQ" Then
.Cells(lRow, 5).Value = "CHQ" & loopy
Else
.Cells(lRow, 5).Value = Me.cboType.Value
End If
Next loopy

End With

End Sub

 

[Nigel]

Add another counter and increment this when the payment type is CHQ use that
number to allocate the payment type CHQ e.g.

Private Sub CmdAdd_Click()
Dim lRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Data")
Dim Counter As Integer
Dim loopy As Integer
Dim ChqNo As Integer
Dim ChqCount as integer

'first empty row
lRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for Payment method entry
If Trim(Me.cboType.Value) = "" Then
Me.cboType.SetFocus
MsgBox "Please select a payment method"
Exit Sub
End If

Counter = lRow - 1

'copy the data to the spreadsheet
ChqCount = 0
With ws
For loopy = 1 To lRow
.Cells(lRow, 1).Value = Counter 'Item No
.Cells(lRow, 3).Value = Me.TxtName.Value 'Payee Name
.Cells(lRow, 4).Value = Me.TxtFee.Value 'Amount

If Left(Cells(lRow, 5).Value, 3) = "CHQ" Then
ChqCount = ChqCount + 1
.Cells(lRow, 5).Value = "CHQ" & ChqCount
Else
.Cells(lRow, 5).Value = Me.cboType.Value
End If
Next loopy

End With

End Sub

 

[Wendy]

Hi Nigel

It is not adding the chqcount to the value chq, and is only displaying chq
in the worksheet cell. I copied your code into mine and am not sure what
the problem is.

Wendy

 

[Nigel]

Try this line instead....

..Cells(lRow, 5).Value = "CHQ" & Cstr(ChqCount)


--

Regards,
Nigel
(e-mail address removed)

 

[Wendy]

Hi Nigel

If I have the Chqcount before the with ws it is ignored completely. If I
have chqcount inside the loop it seems to use the counter value as the
chqcount.

The value is shown as 'Chq' in the form, the form is displayed on a
worksheet called Payment entry, and the data stored on the worksheet called
data.

Wendy