Need help with For-Next problem

[excelnut1954]

In a User Form, the user enters in records to be removed from the list.
He will enter info in the textboxes in the following layout. It will
allow 13 records to be identified in the userform.

Textboxes 1-25 (odd numbers only) are for "PO Numbers" (13 total
records).
Textboxes 2-26 (even numbers only) are for "Taken By".
TextBoxes 27-39 are for "Pieces Moved".
Textbox 41 is the default date to be applied to each record. Together,
this allows for 13 records to be designated for delete. This puts data
in for each of the records. A macro later will actually remove them
from the list.

The For-Next below will look at each of the "PO Number" boxes, and
if there is an entry, will test to make sure it's on the list, and
also for duplicates. Then it will post the "Taken By", "Pieces
Moved", and the default date to that record on the list.

The good news is if all of the textboxes (13 records) are filled, then
this works perfect.

Here's the problem:
If there are any less than 13 PO Numbers entered, then only the 1st
record is done correctly. Only the default date is entered for the
others. The "Pieces Taken", and
"Taken By" data is not.

Can anyone figure out why this will work only if all 13 records are
filled in? I'm just learning about the For-Next loops, so it's
hard for me to see what may be the problem.
Thanks to Bob Phillips for the core of this sub. Maybe Bob, or someone
else can stumble upon this, and figure out what I'm looking for.

Thanks,
J.O.


Declarations
Public rngToSearch As Range
Public rngFound As Range
Public PONum As String
Public CountPOtoValidate As String

Sub DeleteTest()

Dim i As Long

For i = 1 To 13
'This will check to make sure there is 1
'and only 1 of this PO number on list.
Worksheets("Official List").Activate
If Me.Controls("TextBox" & i * 2 - 1).Text <> "" Then
PONum = Me.Controls("TextBox" & i * 2 - 1).Text

' Worksheets("Official List").Activate
CountPOtoValidate = Application.CountIf(Range("J:J"),
PONum)
End If

If CountPOtoValidate < 1 Then
MsgBox "This record does not exist on the list." &
vbNewLine & _
"Please check the PO number and try again"

ElseIf CountPOtoValidate > 1 Then
MsgBox "There are duplicate records." & vbNewLine & _
"Highlight this record on the list, then see the
supervisor."

Else

'This will post the entries from TextBoxes 2, 27 & 41
'for the PO# entered in TextBox1, 3, 5, etc.
Set rngToSearch = Sheets("Official List").Columns("J")
Set rngFound = rngToSearch.Find(What:=PONum, _
LookIn:=xlValues)

rngFound.Select
ActiveWorkbook.Names.Add Name:="DeletePOCell",
RefersTo:=ActiveCell

Application.Goto Reference:="DeletePOCell"
ActiveCell.Offset(0, 4).Select
Application.Selection.Value = _
Me.Controls("TextBox" & 26 + i).Text 'Pieces moved
ActiveCell.Offset(0, 2).Select
Application.Selection.Value = _
UCase(Me.Controls("TextBox" & i * 2).Text) 'Taken By
ActiveCell.Offset(0, 1).Select
Application.Selection.Value = TextBox41.Text 'Default
date
Cancel = False
ActiveWorkbook.Names("DeletePOCell").Delete

End If

Next i

End Sub

 

[Jim Cone]

As I understand it, you want nothing to happen if a odd numbered
text box has no entry. You should be able to accomplish that by
moving the first "End if" down below the last End If (just above Next).
See the two flagged lines below.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


Sub DeleteTest()
Dim i As Long
For i = 1 To 13
'This will check to make sure there is 1
'and only 1 of this PO number on list.
Worksheets("Official List").Activate
If Me.Controls("TextBox" & i * 2 - 1).Text <> "" Then
PONum = Me.Controls("TextBox" & i * 2 - 1).Text
' Worksheets("Official List").Activate
CountPOtoValidate = Application.CountIf(Range("J:J"), PONum)
End If '<<<< Move this

If CountPOtoValidate < 1 Then
MsgBox "This record does not exist on the list." & vbNewLine & _
"Please check the PO number and try again"
ElseIf CountPOtoValidate > 1 Then
MsgBox "There are duplicate records." & vbNewLine & _
"Highlight this record on the list, then see the supervisor."
Else
'This will post the entries from TextBoxes 2, 27 & 41
'for the PO# entered in TextBox1, 3, 5, etc.
Set rngToSearch = Sheets("Official List").Columns("J")
Set rngFound = rngToSearch.Find(What:=PONum, _
LookIn:=xlValues)
rngFound.Select
ActiveWorkbook.Names.Add Name:="DeletePOCell", RefersTo:=ActiveCell
Application.Goto Reference:="DeletePOCell"
ActiveCell.Offset(0, 4).Select
Application.Selection.Value = _
Me.Controls("TextBox" & 26 + i).Text 'Pieces moved
ActiveCell.Offset(0, 2).Select
Application.Selection.Value = _
UCase(Me.Controls("TextBox" & i * 2).Text) 'Taken By
ActiveCell.Offset(0, 1).Select
Application.Selection.Value = TextBox41.Text 'Default Date
Cancel = False
ActiveWorkbook.Names("DeletePOCell").Delete
End If
'<<<< Move it here
Next i

End Sub

 

[excelnut1954]

I changed it. It's different now. Now, I get all but the last one. I
entered 11 records, and 10 posted ok. The 11th one only posted the
date. I entered other quantities, and it reacts the same.... all but
the last record (unless I enter 13, which is the max, then it works
fine).
Thanks for your help. If you have other suggestions, I'd appreaciate
it.
J.O.

 

[Jim Cone]

This is a simplified version of your code.
I don't know if the problem is fixed (or even if the code will run)
as I cannot test it.
If you still have problems , uncomment the stop command in the loop
and step thru the code (F8) to find out what is going on.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Sub DeleteTest()
Dim i As Long
Dim rngToSearch As Range
Dim rngFound As Range
Dim PONum As String
Dim CountPOtoValidate As String
Set rngToSearch = Me.Columns("J")

For i = 1 To 13
'This will check to make sure there is 1
'and only 1 of this PO number on list.
Worksheets("Official List").Activate
If Me.Controls("TextBox" & i * 2 - 1).Text <> "" Then
PONum = Me.Controls("TextBox" & i * 2 - 1).Text
CountPOtoValidate = Application.CountIf(Me.Range("J:J"), PONum)
If CountPOtoValidate < 1 Then
MsgBox "This record does not exist on the list." & vbNewLine & _
"Please check the PO number and try again. "
ElseIf CountPOtoValidate > 1 Then
MsgBox "There are duplicate records." & vbNewLine & _
"Highlight this record on the list, then see the supervisor. "
Else
'This will post the entries from TextBoxes 2, 27 & 41
'for the PO# entered in TextBox1, 3, 5, etc.
Set rngFound = rngToSearch.Find(What:=PONum, LookIn:=xlValues)

' if i = 11 then Stop 'Uncomment to step thru code

If Not rngFound Is Nothing Then
rngFound.Offset(0, 4).Value = _
Me.Controls("TextBox" & 26 + i).Text 'Pieces moved
rngFound.Offset(0, 6).Value = _
UCase$(Me.Controls("TextBox" & i * 2).Text) 'Taken By
rngFound.Offset(0, 7).Value = TextBox41.Text 'Default Date
Else
MsgBox "Unable to find PO number " & PONum & " "
End If
End If
End If
Set rngFound = Nothing
Next i
End Sub
'--------------


I changed it. It's different now. Now, I get all but the last one. I
entered 11 records, and 10 posted ok. The 11th one only posted the
date. I entered other quantities, and it reacts the same.... all but
the last record (unless I enter 13, which is the max, then it works
fine).
Thanks for your help. If you have other suggestions, I'd appreaciate
it.
J.O.

 

[excelnut1954]

Thanks, Jim
I'll give it a shot. Time to learn more about debugging.
Thanks again
J.O.