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
|