I have a database that has Cow numbers in Column B and vaccination dates in
the proceeding 5 columns (C, D, E, F, G)for each cow.
Each week We open this excel VBA based workbook and, using a pivot table
lookup what cows are due for vaccinations. Normally we have to individually
go through the database and enter the vaccination date into what column (C,
D, E, F, or G) it applies. Each column is a different vaccination and each
cow must complete all vaccinations in our program.
I already have a userform with a list box that displays the cow numbers that
we vaccinated that day-what I need to do is be able to enter a date in that
same userform and have it entered in the first empty column in the main
database that corresponds to each cow in the listbox.
I have no idea how to make multiple action proceed from a listbox of
selected items.

Any help would be greatly appreciated-thanks

A couple questions:
1. If cow #1 has nothing in column C and you vaccinate for "Col-C Vaccine",
then you store the date in Column C. But if there is a date in that column
but it was "expired", then do you record the "Col-C Vaccine" in column C,
this time wiping out the old date that you replaced.

2. Do you always record a specific vaccine's date in a a specific column?
That is what it at first sounded like, but then later in your text, it led me
to believe that was not necessarily the case.

Based on your answers to these questions, this code might change a bit, but
this is the idea I have for doing this:

Sub DoForm()
Dim TheDate As Date
Dim X As Long
Dim NbrVaccined As Integer '
Dim Y As Integer

UserForm1.Show

If UserForm1.datefield = True Then
Let TheDate = UserForm1.datefield
Else
Exit Sub
End If

'get your cow #'s etc. Let's assume you assigned them to an array:
CowList(5,2) where the
'first element is the Cow #, the second # is the vaccine Type. Now you have
all the parts:

'just go to the list and write the data out:

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, 1).Value = cowlist(Y, 1) Then
If cowlist(Y, 2) = 1 Then '(or whatever you use to differentiate
the type of vaccine)
Cells(X, 2).Value = TheDate
ElseIf cowlist(Y, 2) = 2 Then
Cells(X, 3).Value = TheDate
'etc....
End If
End If
Next
Loop



End Sub

HTH!

Hi Mike-First Thank you.

Let me answer those Qs:
#1:Once the cow gets a certain vaccination say in column C then her next
would be recorded in Column D. None of them expire-so no dates would be
replaced ever. Once the cow gets all her vaccinations she is removed from the
database-so no replacement of cell data occurs.
#2:Specific vaccines go in specific columns yes. Each column is labeled with
the name of the vaccination. (Example-Column C="12 month Vacc.", Column
D="14Month Vacc.")
So yes they are specific that is why is has been a little difficult to have
multiple cows that could be getting different vaccinations be updates at
once. What I had set up is a status cell at the end of each row that updates
each cow's vaccination status depending upon how many cells within their row
is filled. (Example-If only cells through Column C are filled then that cow's
vaccination status would say "14Month" because that is the vacc she is due
for next. )

Its been an interesting program to work out. But basically if there is a way
to individually look at the selected cow's row and fill in their last empty
column with a date then their status is updated and this process does not
have to be done manually for each cow.

Again thank you for your help-let me know if I have answered your two
questions-I will give the code a go today!!

"Mike H." wrote:

A couple questions:
1. If cow #1 has nothing in column C and you vaccinate for "Col-C Vaccine",
then you store the date in Column C. But if there is a date in that column
but it was "expired", then do you record the "Col-C Vaccine" in column C,
this time wiping out the old date that you replaced.

2. Do you always record a specific vaccine's date in a a specific column?
That is what it at first sounded like, but then later in your text, it led me
to believe that was not necessarily the case.

Based on your answers to these questions, this code might change a bit, but
this is the idea I have for doing this:

Sub DoForm()
Dim TheDate As Date
Dim X As Long
Dim NbrVaccined As Integer '
Dim Y As Integer

UserForm1.Show

If UserForm1.datefield = True Then
Let TheDate = UserForm1.datefield
Else
Exit Sub
End If

'get your cow #'s etc. Let's assume you assigned them to an array:
CowList(5,2) where the
'first element is the Cow #, the second # is the vaccine Type. Now you have
all the parts:

'just go to the list and write the data out:

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, 1).Value = cowlist(Y, 1) Then
If cowlist(Y, 2) = 1 Then '(or whatever you use to differentiate
the type of vaccine)
Cells(X, 2).Value = TheDate
ElseIf cowlist(Y, 2) = 2 Then
Cells(X, 3).Value = TheDate
'etc....
End If
End If
Next
Loop



End Sub

HTH!

'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

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

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