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
--
Regards,
Nigel
"Wendy" wrote in message
...
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" & ChqCount
Else
.Cells(lRow, 5).Value = Me.cboType.Value
End If
Next loopy
End With
End Sub