I am confused too. I don't see how you know which cows to update vaccines
on? Your original posting said you use a pivot table to determine which cows
are due for a vaccine. So you should take that list and either use the list
to populate cowlist() or do something else to find the cows that need to have
vaccines updated. Then just use the if-then-else statement as you have below
to determine which column to populate the date in. HTH...

"M.Desmond" wrote:


Here is the code I attached to a Userform2 command button
I am just confused about my database and the cowlist I think. :(

Private Sub cmdupdate_Click()


Sheets("database").Select
Dim TheDate As Date
Dim X As Long
Dim NbrVaccined As Integer '
Dim Y As Integer
Dim CowList As Range



If UserForm2.datefield = True Then
Let TheDate = UserForm2.datefield
Else
Exit Sub
End If
Range("CowList").Select
X = ActiveCell.Row
Do While True
If Cells(X, 1).Value = Empty Then Exit Do 'if there is no cow # in col A,
then you're done.
For Y = 1 To NbrVaccined
If Cells(X, 2).Value = CowList(Y, 1) Then
If Cells(X, 3).Value = Empty Then
Cells(X, 3).Value = TheDate
Exit For
ElseIf Cells(X, 4).Value = Empty Then
Cells(X, 4).Value = TheDate
Exit For
ElseIf Cells(X, 5).Value = Empty Then
Cells(X, 5).Value = TheDate
Exit For
ElseIf Cells(X, 6).Value = Empty Then
Cells(X, 6).Value = TheDate
Exit For
ElseIf Cells(X, 7).Value = Empty Then
Cells(X, 7).Value = TheDate
Exit For
End If
End If
Next
X = X + 1
Loop


End Sub
"M.Desmond" wrote:

Hi Mike-
Thanks for your help so far. I have been tweeking your code a little to fit
in and had a few questions-

1: what is the significance of "NbrVaccinated" is it a named range?, a
txtbox? or simply a preset that does not need to be messed with?
2: If my database is on a different sheet called "database" (Same workbook
though) from my named range of cows that need to have their vaccinations
updated does that pose a problem?

I was also just having a little trouble telling if it would be best to have
the date come from a form, then into a worksheet and then used or if I should
skip the form all together, simply using a form to create a two columned list
(cow # and Vaccination date)?

Thank you again for your help

"Mike H." wrote:

'this snippet of code should work. What it does is it goes down your list of
cows and when it finds a cow # that is in your cowlist() array, it then
populates the next empty cell, either column c, d,e,f or g. HTH

Range("CowList").Select
X = ActiveCell.Row
Do While True
If Cells(X, 1).Value = Empty Then Exit Do 'if there is no cow # in col
A, then you're done.
For Y = 1 To NbrVaccined
If Cells(X, 2).Value = CowList(Y, 1) Then
If Cells(X, 3).Value = Empty Then
Cells(X, 3).Value = TheDate
Exit For
ElseIf Cells(X, 4).Value = Empty Then
Cells(X, 4).Value = TheDate
Exit For
ElseIf Cells(X, 5).Value = Empty Then
Cells(X, 5).Value = TheDate
Exit For
ElseIf Cells(X, 6).Value = Empty Then
Cells(X, 6).Value = TheDate
Exit For
ElseIf Cells(X, 7).Value = Empty Then
Cells(X, 7).Value = TheDate
Exit For
End If
End If
Next
X = X + 1
Loop