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

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

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" wrote in message
...
Add another counter and increment this when the payment type is CHQ use
that number to allocate the payment type CHQ e.g.

Nigel




"Wendy" wrote in message
...

Try this line instead....

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


--

Regards,
Nigel




"Wendy" wrote in message
...
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" wrote in message
...
Add another counter and increment this when the payment type is CHQ use
that number to allocate the payment type CHQ e.g.

Nigel




"Wendy" wrote in message
...

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


"Nigel" wrote in message
...
Try this line instead....

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


--

Regards,
Nigel




"Wendy" wrote in message
...
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" wrote in message
...
Add another counter and increment this when the payment type is CHQ use
that number to allocate the payment type CHQ e.g.

Nigel




"Wendy" wrote in message
...