Code runs without error but doesn't print output

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>
>