Hi Everyone

I am using the following code to make a userform enter into a worksheet the
data collected following a button press. It does not find the cell as it
gives NA in the messagebox.

If I do Not make an entry in the textbox (txtdate) the code does search and
run correctly because it finds the next blank cell in the column and returns
$A$96

I have thought it may be due to the way that the formatting was set and that
it might not match in the computers eye. Do I need CLng or is it not this?

The calendar name is calendar1 and I have tried this in the place of
txtdate.value

If you have any ideas please let me know.

Rob

Code ****
Private Sub cmdenterinfo_Click()

Dim TheDate As String
Dim CellAddr As Variant

ActiveWorkbook.Sheets("York Naburn").Activate

TheDate = txtdate.Value

CellAddr = FindDateAddress("York Naburn", "A", TheDate)

MsgBox "The date " & TheDate & " is in cell " & CellAddr

If Not CellAddr = "NA" Then
With Range(CellAddr)
.Offset(0, 3) = txtnaburncrude.Value
.Offset(0, 4) = txtsbr1amm.Value
.Offset(0, 5) = txtsbr1bod.Value
.Offset(0, 6) = txtsbr2amm.Value
.Offset(0, 7) = txtsbr2bod.Value
.Offset(0, 8) = txtsbr3amm.Value
.Offset(0, 9) = txtsbr3bod.Value
.Offset(0, 10) = txtsbr4amm.Value
.Offset(0, 11) = txtsbr4bod.Value
.Offset(0, 12) = txt3wksamm.Value
.Offset(0, 13) = txt3wksbod.Value
.Offset(0, 18) = txtsbr1sbl.Value
.Offset(0, 19) = txtsbr2sbl.Value
.Offset(0, 20) = txtsbr3sbl.Value
.Offset(0, 21) = txtsbr4sbl.Value
.Offset(0, 24) = txtsbr1mlss.Value
.Offset(0, 25) = txtsbr1mlss.Value
.Offset(0, 26) = txtsbr1mlss.Value
.Offset(0, 27) = txtsbr1mlss.Value
End With
End If

Range("A1").Select

Unload Me

End Sub

Public Function FindDateAddress(SheetName, ColLetter, TheDate) As String

Dim c As Variant

With Worksheets(SheetName).Columns(ColLetter & ":" & ColLetter)
Set c = .Find(TheDate, LookIn:=xlFormulas, lookat:=xlPart)
If Not c Is Nothing Then
FindDateAddress = c.Address
Else
FindDateAddress = "NA"
End If
End With
End Function

Robert,

Try this, since TheDate is a string:

Set c = .Find(DateValue(TheDate), LookIn:=xlFormulas, lookat:=xlPart)

HTH,
Bernie
MS Excel MVP


"Robert Hargreaves" wrote in message
...
Hi Everyone

I am using the following code to make a userform enter into a worksheet
the
data collected following a button press. It does not find the cell as it
gives NA in the messagebox.

If I do Not make an entry in the textbox (txtdate) the code does search
and
run correctly because it finds the next blank cell in the column and
returns
$A$96

I have thought it may be due to the way that the formatting was set and
that
it might not match in the computers eye. Do I need CLng or is it not
this?

The calendar name is calendar1 and I have tried this in the place of
txtdate.value

If you have any ideas please let me know.

Rob

Code ****
Private Sub cmdenterinfo_Click()

Dim TheDate As String
Dim CellAddr As Variant

ActiveWorkbook.Sheets("York Naburn").Activate

TheDate = txtdate.Value

CellAddr = FindDateAddress("York Naburn", "A", TheDate)

MsgBox "The date " & TheDate & " is in cell " & CellAddr

If Not CellAddr = "NA" Then
With Range(CellAddr)
.Offset(0, 3) = txtnaburncrude.Value
.Offset(0, 4) = txtsbr1amm.Value
.Offset(0, 5) = txtsbr1bod.Value
.Offset(0, 6) = txtsbr2amm.Value
.Offset(0, 7) = txtsbr2bod.Value
.Offset(0, 8) = txtsbr3amm.Value
.Offset(0, 9) = txtsbr3bod.Value
.Offset(0, 10) = txtsbr4amm.Value
.Offset(0, 11) = txtsbr4bod.Value
.Offset(0, 12) = txt3wksamm.Value
.Offset(0, 13) = txt3wksbod.Value
.Offset(0, 18) = txtsbr1sbl.Value
.Offset(0, 19) = txtsbr2sbl.Value
.Offset(0, 20) = txtsbr3sbl.Value
.Offset(0, 21) = txtsbr4sbl.Value
.Offset(0, 24) = txtsbr1mlss.Value
.Offset(0, 25) = txtsbr1mlss.Value
.Offset(0, 26) = txtsbr1mlss.Value
.Offset(0, 27) = txtsbr1mlss.Value
End With
End If

Range("A1").Select

Unload Me

End Sub

Public Function FindDateAddress(SheetName, ColLetter, TheDate) As String

Dim c As Variant

With Worksheets(SheetName).Columns(ColLetter & ":" & ColLetter)
Set c = .Find(TheDate, LookIn:=xlFormulas, lookat:=xlPart)
If Not c Is Nothing Then
FindDateAddress = c.Address
Else
FindDateAddress = "NA"
End If
End With
End Function