Hi all,
I have to write a code wherein i have to check if the month in Column E is Sep. I have to then compare this with the current month and then accordingly decide if the data gets copied to a sheet or gets ignored.
I am not able to compare the months. What i am using is month(date) compared to month(range("E2").value).
Any suggestions?
Last edited by arlu1201; 10-04-2011 at 07:28 AM.
Why doesn't work your method? Show an example of data.
As your range object doesn't have a qualifying workbook or worksheet are you sure that you're looking at the cell you think you are?
If column E is formatted tot string you either:
- compare strings
- or convert the string to a date:
if instr(range("E2").Text,monthname(month(date)))>0 or if instr(range("E2").Text,format(date,"mmmm"))>0
if month(date)=month(cdate(Range("E2")))
Last edited by snb; 10-04-2011 at 06:46 AM.
Andrew-R,
Even if i put
It gives me a type mismatch error.If Month(CDate(Worksheets(i).Range("E" & j).Value)) = Month(Date)
snb,
None of the 2 formats are working.
What's i? What's j? Post your file
I have attached the file and the code is in module 1.
You're trying to compare a string value ("SEP") with a date value - that will be your problem.
If you use Format(Month(Date),"mmm") in your comparison then that will get you past that and on to the next problem with your code
Thank you Andrew-R.
Just one question - should i format the "sep" in the sheet or should i format today's date which i will be comparing to "sep"?
I actually need the numerical value of the month, so that i can compare it to the other rows as well. I need to ascertain if its equal to this month's number (10), then it should go into the "30 day" tab, if its > than this month, then the "60 day" tab.
You are getting a type mismatch because column E holds strings, not dates.
OK, first of all I just messed up the code, so ignore what I wrote.
In your code the line doing the comparison should be:
If Range("E" & j).Value = Format(Date, "mmm") Then
That should work fine. It doesn't change the format of anything on your sheet, but it will work.
On the other hand, if your sheet is working with dates then you'd be better off storing them as dates, rather than as strings.
these values in the sheet are text. The only format you can apply to them is cosmetic. Use real dates in these cells, and THEN you can format them to show just the month (using a custom format)should i format the "sep" in the sheet
I have actually obtained this sheet from someone else who needs help with VBA. I will try and have them change the values so it will be much easier to code it.
Thank you all.
That would be this question, then?I have actually obtained this sheet from someone else who needs help with VBA.
If you get stuck with a question, you can post into the Cavalry thread in the Water Cooler to call in more expertise. For the OP that will be a lot faster than waiting until you've received answers on your question.
Last edited by teylyn; 10-04-2011 at 07:45 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks