Pretty easy If loop - help!!

N

nemadrias

How can I use an if loop to return the first number of a date.

For example, if cell A1 has the date: 7/06/2006, I want to say:

If (ActiveCell.Characters(Start:=1, Length:=1).Text = "7") Then
TodayInt = 7
End If

But this is trying to look for a string and its finding a date, so I
get an error...Anyone have a better idea??
 
K

Kevin Vaughn

It looks like you are trying to get the first character of a date. What if
the month is January, October, November, or December? How would you know
which it is? Why not use something like month(activecell.value)
 
N

Nick Hodge

Having read Kevin's reply it could be the date you show is US mm/dd/yyyy. I
was presuming UK dd/mm/yyyy.

Take your pick depending on locale, but you should now get the drift

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
[email protected]
 
K

Kevin Vaughn

Good point. I ALWAYS assume US format (because that is what I am used to,)
and I frequently get burned because of it.
 
N

nemadrias

Guys -
Thanks - it seems to compile and run, but still not working. Here's
the update:
It is M/D/YY - I fall into the same mistake sometimes!
I'm calling the following from a different module:

If Month(ActiveCell.Value) = 7 Then
TodayInt = 7
End If

But when I go through the debugger, it says TodayInt is 0 still. Any
clues why??
Thanks so much both of you.
Steve
 
N

nemadrias

Sorry - quick update:

It is now
If Month(ActiveCell.Value) = "July" Then
TodayInt = 7
End If

In the format July-06.

Same problem however...
 
K

Kevin Vaughn

What does the debugger say activecell.value is? It would need to be an date
in order for the Month function (or day function) to work correctly. Have
you tried stepping through the program to see if it goes to the line that
sets todayint to 7? I just now took another look at your example and that
certainly looks like a date. What would make that not work? Hmm, I don't
know. I tried several things like making the activecell look like a date but
with an apostrophe in front or with a space or even several spaces, and each
time I did ? month(activecell.value) from the immediate window, it printed
the correct month.

Sorry, I can't think of anything else right now. Good luck.
 
N

nemadrias

Kevin -
Thanks for your help and insights. I ended up using this expression:

If ActiveCell.Value Like "1*" Then
TodayInt = 1

and moving it into the current module where it worked just fine. I
also changed the date format back to m-yy, but I had to make it a text
string from a date. =TEXT(A1, "m-yy")

So after a long beat around the bush I found a solution with your help.
Thanks again,
Have a great weekend.
Steve
 
K

Kevin Vaughn

Well, it doesn't sound like what I suggested would have helped, but glad you
got it working!
 
Top