Fighting a date format

[jeremiah]

I need the following to return the value of the cell, not the word "TRUE", I
have tried multiple ways to get this with no luck. Am sure it is an obvious
change.

For Each cell In Range("b:b")
If cell.Value Like "* Totals" Then
cell.Offset(5, 0) = IsDate(Range("C3"))
selection.NumberFormat = "m/d/yyyy"
End If

 

[Rick Rothstein]

IsDate is a function that looks to see if a cell contains a date or not,
hence the TRUE value. Take that function away and you will get the contents
of C3 instead...

cell.Offset(5, 0) = Range("C3")

However, your next line is formatting the Selection... the offset cell you
just assigned the value to is not your selection. I *think* you may want
this instead...

cell.Offset(5, 0).NumberFormat = "m/d/yyyy"

 

[Chip Pearson]

The IsDate function returns True or False indicating whether the input
value is in fact a date. In your code, IsDate evaluates the contents
of C3 and if it is a date, IsDate returns True. If C3 is not a date,
IsDate returns False.

You can simply get rid of IsDate:

Change
cell.Offset(5, 0) = IsDate(Range("C3"))
to
cell.Offset(5, 0) = Range("C3").Value
or
cell.Offset(5,0) = Format(Range("C3").Value, "m/d/yyyy")

selection.NumberFormat = "m/d/yyyy"

This refers to the cell(s) that is presently selected, which very well
may not be cell.Offset(5,0). It is probably better to use

cell.Offset(5,0).NumberFormat = "m/d/yyyy"

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[meh2030]

I need the following to return the value of the cell, not the word "TRUE", I
have tried multiple ways to get this with no luck.  Am sure it is an obvious
change.

For Each cell In Range("b:b")
    If cell.Value Like "* Totals" Then
        cell.Offset(5, 0) = IsDate(Range("C3"))
        selection.NumberFormat = "m/d/yyyy"
    End If

Jeremiah,

It may help for you to see how your program is behaving. You might
want to inswer the following code, step through your program (F8), and
watch the Immediate Window (View | Immediate Window).

For Each cell In Range("b:b")
Debug.Print "cell Addrs:"; cell.Address
If cell.Value Like "* Totals" Then
cell.Offset(5, 0) = IsDate(Range("C3"))
Debug.Print "cell Offset Addrs:"; cell.Offset(5,
0).Address
Selection.NumberFormat = "m/d/yyyy"
Debug.Print "selection Addrs:"; Selection.Address
End If
Next

I think you may be looking for the following (but I'm not sure because
I don't know what your exact objective is):

For Each cell In Range("b:b")
If cell.Value Like "* Totals" Then
If IsDate(cell.Offset(5, 0).Value) Then
cell.Offset(5,0).NumberFormat = "m/d/yyyy"
End If
End If
Next