This is hard to explain why I do it this way but I will try. I have 5 cost
items that get updated occasionly. Not all 5 get updated, but at least one
will when the userform is started. In the userform activate code I set 2
labels and a dtpicker for each cost item that has been changed. The cost
items that have not been changed I set the labels and dtpicker visible to
false. The way the code is written they will always be in order 1,2,3, etc.
so the subven should always been subven(1,2),subven(2,2),etc. It shouldn't
skip the first number in the array. Once the subven array has collected all
the values, I then find the cost item using set fcell = .find******** once it
finds the right cell it then offsets to the correct cell to put the new date
in.

"Rick Rothstein (MVP - VB)" wrote:

I'm not so sure I like the logic of your code. Your assignment of DatePicker
dates is contingent on Label controls being visible, so it would seem if the
label is not visible, nothing is assigned to the array for that element and
increment a counter. HOWEVER, when you assign the array elements to the
cells, you just iterate through the counter without worrying about which
DatePicker assignments caused the counter to increase. For example, if the
Label5 and Label7 are not visible and Label9 and Label11 are (it appears
Label2 always is visible), your counter will increment to 3 and the array
assignments will be to subven(1, 2), subven(4, 2) and subven(5, 2). However,
when you assign these to the cells, you will be assigning subven(1, 2),
subven(2, 2), subven(3, 2) because your loop iterates from 1 to cntr (your
counter)... those last two array elements do NOT have dates in them (so I'm
guessing 0 gets assigned to the cells)!

Rick


"ranswrt" wrote in message
...
I have the following code that takes the date from 5 DTPickers on a
userform
the putts them into cell on a worksheet.

Private Sub UserForm_Terminate()
Dim subven(5, 2) As Variant
Dim db As String
Dim nme As String
Dim cntr As Integer
Dim i As Integer
Dim rng As Range
Dim fcell As Range


db = Range("currentdb")
nme = Replace(db, " ", "")
nme = LCase(nme)
Call stopautocalc

cntr = 1
subven(1, 1) = Label2.caption
subven(1, 2) = DTPicker1.Value

If Label5.Visible = True Then
cntr = cntr + 1
subven(2, 1) = Label5.caption
subven(2, 2) = DTPicker2.Value
End If

If Label7.Visible = True Then
cntr = cntr + 1
subven(3, 1) = Label7.caption
subven(3, 2) = DTPicker3.Value
End If

If Label9.Visible = True Then
cntr = cntr + 1
subven(4, 1) = Label9.caption
subven(4, 2) = DTPicker4.Value
End If

If Label11.Visible = True Then
cntr = cntr + 1
subven(5, 1) = Label11.caption
subven(5, 2) = DTPicker5.Value
End If

Sheets(db & " db").Unprotect Password:="****"
Set rng = Range(nme & "subvenrng")
For i = 1 To cntr
With rng
Set fcell = .Find(what:=subven(i, 1), LookIn:=xlValues,
LookAt:=xlWhole, _
SearchOrder:=xlByColumns)
fcell.Offset(0, 1) = subven(i, 2)
End With
Next

Sheets(db & " db").Protect Password:="****"
Call startautocalc

End Sub

The problem I am having is that it is putting the wrong date into 'fcell'.
The date it is putting is '1/0/1900' instead of the date that was selected
with the DTPicker control. I have formatted the cell 'date mm/dd/yy'. I
have also tried other number formats. Any suggestions on what to do?