Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple updates to database
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple updates to database
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple updates to database
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple updates to database
'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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple updates to database
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple updates to database
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple updates to database
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple updates to database
Hi, Sorry for the confusion mike. Here is the layout. I have a date/time counter that relates each cow number to today's date and in one way or another returns "due" in its column (J). The pivot table then makes a list of those due cows. I then had a list box form that would collect the pivot table cow numbers and ask the user to check off within the listbox which cows they actually did vaccinate . From there a new list on a totally different sheet is made. I was making that array=CowList it was an offset named list and had cow numbers in A and the vaccination date in column B. Then What I was hoping to do was have a command button or maybe another form that looked at the new array CowList and tried to find those cow numbers in column B of my main sheet called "database." I think your code is totally on the money, I just am having a hard time understanding how to implement it. Should I attach that code to a command button on my sheet with the CowList or should I make it a Macros...? This is where I am simply lacking experience and I apologize. Hoping you have some patience left- Thank you for your time, Mae "Mike H." wrote: 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... |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple updates to database
Put this code on a button anywhere and it should do the trick.
Private Sub CommandButton1_Click() Dim X As Long Dim Fnd As Long Dim DataArray(5000, 2) As Variant 'if tempsheet is on another workbook then this line too: windows("tempsheet").activate Sheet("Tempsheet").Select X = 1 Do While True If Cells(X, 1).Value = Empty Then Exit Do Fnd = Fnd + 1 DataArray(Fnd, 1) = Cells(X, 1).Value DataArray(Fnd, 2) = Cells(X, 2).Value X = X + 1 Loop 'now we have an array, dataarray, which has all the cow #'s (From Column 1) and all the vaccine dates from col 2 'if database is in a different workbook: Windows("database.xls").Activate Sheet("database").Select X = 1 Do While True If Cells(X, 2).Value = Empty Then Exit Do For y = 1 To Fnd If DataArray(y, 1) = Cells(X, 2).Value Then If Cells(X, 3).Value = Empty Then Cells(X, 3).Value = DataArray(y, 2) Exit For ElseIf Cells(X, 4).Value = Empty Then Cells(X, 4).Value = DataArray(y, 2) Exit For ElseIf Cells(X, 5).Value = Empty Then Cells(X, 5).Value = DataArray(y, 2) Exit For ElseIf Cells(X, 6).Value = Empty Then Cells(X, 6).Value = DataArray(y, 2) Exit For ElseIf Cells(X, 7).Value = Empty Then Cells(X, 7).Value = DataArray(y, 2) Exit For End If End If Next X = X + 1 Loop End Sub |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple updates to database
Mike-thank you so much, it works flawlessly!
have a great rest of your week -mae "Mike H." wrote: Put this code on a button anywhere and it should do the trick. Private Sub CommandButton1_Click() Dim X As Long Dim Fnd As Long Dim DataArray(5000, 2) As Variant 'if tempsheet is on another workbook then this line too: windows("tempsheet").activate Sheet("Tempsheet").Select X = 1 Do While True If Cells(X, 1).Value = Empty Then Exit Do Fnd = Fnd + 1 DataArray(Fnd, 1) = Cells(X, 1).Value DataArray(Fnd, 2) = Cells(X, 2).Value X = X + 1 Loop 'now we have an array, dataarray, which has all the cow #'s (From Column 1) and all the vaccine dates from col 2 'if database is in a different workbook: Windows("database.xls").Activate Sheet("database").Select X = 1 Do While True If Cells(X, 2).Value = Empty Then Exit Do For y = 1 To Fnd If DataArray(y, 1) = Cells(X, 2).Value Then If Cells(X, 3).Value = Empty Then Cells(X, 3).Value = DataArray(y, 2) Exit For ElseIf Cells(X, 4).Value = Empty Then Cells(X, 4).Value = DataArray(y, 2) Exit For ElseIf Cells(X, 5).Value = Empty Then Cells(X, 5).Value = DataArray(y, 2) Exit For ElseIf Cells(X, 6).Value = Empty Then Cells(X, 6).Value = DataArray(y, 2) Exit For ElseIf Cells(X, 7).Value = Empty Then Cells(X, 7).Value = DataArray(y, 2) Exit For End If End If Next X = X + 1 Loop End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Database updates | Excel Discussion (Misc queries) | |||
Mulitple updates in database | Excel Programming | |||
Excel Multiple sheet updates | Excel Worksheet Functions | |||
Set up database that updates automatically Qty's entered | Excel Worksheet Functions | |||
Drawing driven database updates | Excel Discussion (Misc queries) |