| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pretty easy If loop - help!!
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?? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Pretty easy If loop - help!!
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) -- Kevin Vaughn "nemadrias" wrote: 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?? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Pretty easy If loop - help!!
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 HIS "nemadrias" wrote in message oups.com... 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?? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Pretty easy If loop - help!!
Good point. I ALWAYS assume US format (because that is what I am used to,)
and I frequently get burned because of it. -- Kevin Vaughn "Nick Hodge" wrote: 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 HIS "Nick Hodge" wrote in message ... 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 HIS "nemadrias" wrote in message oups.com... 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?? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Pretty easy If loop - help!!
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 Kevin Vaughn wrote: Good point. I ALWAYS assume US format (because that is what I am used to,) and I frequently get burned because of it. -- Kevin Vaughn "Nick Hodge" wrote: 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 HIS "Nick Hodge" wrote in message ... 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 HIS "nemadrias" wrote in message oups.com... 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?? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Pretty easy If loop - help!!
Sorry - quick update:
It is now If Month(ActiveCell.Value) = "July" Then TodayInt = 7 End If In the format July-06. Same problem however... nemadrias wrote: 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 Kevin Vaughn wrote: Good point. I ALWAYS assume US format (because that is what I am used to,) and I frequently get burned because of it. -- Kevin Vaughn "Nick Hodge" wrote: 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 HIS "Nick Hodge" wrote in message ... 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 HIS "nemadrias" wrote in message oups.com... 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?? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Pretty easy If loop - help!!
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. -- Kevin Vaughn "nemadrias" wrote: 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 Kevin Vaughn wrote: Good point. I ALWAYS assume US format (because that is what I am used to,) and I frequently get burned because of it. -- Kevin Vaughn "Nick Hodge" wrote: 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 HIS "Nick Hodge" wrote in message ... 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 HIS "nemadrias" wrote in message oups.com... 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?? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Pretty easy If loop - help!!
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 Kevin Vaughn wrote: 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. -- Kevin Vaughn "nemadrias" wrote: 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 Kevin Vaughn wrote: Good point. I ALWAYS assume US format (because that is what I am used to,) and I frequently get burned because of it. -- Kevin Vaughn "Nick Hodge" wrote: 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 HIS "Nick Hodge" wrote in message ... 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 HIS "nemadrias" wrote in message oups.com... 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?? |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Pretty easy If loop - help!!
Well, it doesn't sound like what I suggested would have helped, but glad you
got it working! -- Kevin Vaughn "nemadrias" wrote: 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 Kevin Vaughn wrote: 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. -- Kevin Vaughn "nemadrias" wrote: 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 Kevin Vaughn wrote: Good point. I ALWAYS assume US format (because that is what I am used to,) and I frequently get burned because of it. -- Kevin Vaughn "Nick Hodge" wrote: 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 HIS "Nick Hodge" wrote in message ... 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 HIS "nemadrias" wrote in message oups.com... 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?? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Adding in loop | Charts and Charting in Excel | |||
| Loop time seems dependent on unrelated workbook - Why? | Excel Worksheet Functions | |||
| Loop gone crazy | Excel Discussion (Misc queries) | |||
| Do Loop | Excel Discussion (Misc queries) | |||
| How easy is it to loop in VBA based on cell contents? | Excel Worksheet Functions | |||
Linear Mode
