Pretty easy If loop - help!!

[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??

 

[Guest]

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)

 

[Nick Hodge]

Use the VBA Day method

If Day(ActiveCell.value)=7 Then
TodayInt = 7
End If


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[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
(e-mail address removed)

 

[Guest]

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

 

[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

 

[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...

 

[Guest]

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.

 

[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

 

[Guest]

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