Hey All, I'm a C#/C++ programmer, and through knowldge of them I can get by in excel/vba, but i'm definitely no Excel Pro yet. I am however willing to learn, which is why I signed up here and browse these forums all day trying to answer each question. Through doing this I learn a lot, by seeing how I'm right, or how I'm wrong and learning from other answers.
Anyways, another thread from someone else has posted for a request to check dates within 3 months of the current date and update another cell's status to "Due Soon"/"Not Due Yet" etc...
Ive written the following
Code:
Sub CheckDates()
Dim r As String
Dim TargetNumMonth As Integer
r = Left(Range("A1").Value, 3)
Select Case r
Case "Jan"
TargetNumMonth = 1
Case "Feb"
TargetNumMonth = 2
Case "Mar"
TargetNumMonth = 3
Case "Apr"
TargetNumMonth = 4
Case "May"
TargetNumMonth = 5
Case "Jun"
TargetNumMonth = 6
Case "Jul"
TargetNumMonth = 7
Case "Aug"
TargetNumMonth = 8
Case "Sep"
TargetNumMonth = 9
Case "Oct"
TargetNumMonth = 10
Case "Nov"
TargetNumMonth = 11
Case "Dec"
TargetNumMonth = 12
End Select
Dim curDate As Date
curDate = Now()
Dim res As String
res = Left(curDate, 1)
Dim CurMonth As Integer
Select Case res
Case 1
CurMonth = 1
Case 2
CurMonth = 2
Case 3
CurMonth = 3
Case 4
CurMonth = 4
Case 5
CurMonth = 5
Case 6
CurMonth = 6
Case 7
CurMonth = 7
Case 8
CurMonth = 8
Case 9
CurMonth = 9
Case 10
CurMonth = 10
Case 11
CurMonth = 11
Case 12
CurMonth = 12
End Select
If TargetNumMonth - CurMonth >= 3 Then
Range("B2").Offset(0, 1).Value = "Not Due Yet"
ElseIf TargetNumMonth - CurMonth < 3 And TargetNumMonth - CurMonth >= 0 Then
Range("B1").Offset(0, 1).Value = "Due Soon"
Else
Range("B1").Offset(0, 1).Value = "Overdue"
End If
End Sub
This ONLY works for cells A1 (prints result in B1). I'm looking for help on making this work for a given range (such as A1:A10).
Also, i realize that the second switch statement is probably useless (wasnt sure if it was ok to compare strings to ints later in code, so i converted in the switch statement instead.
I tried doing:
Code:
With activesheet.Range("A1:A10")
for each c in .Cells
r = Left(Range(ActiveCell).Value, 3)
Of course this wont work as ActiveCell isnt a variable/type, but it's pseudo of how i want to do it. Anyway someone can point me in the right direction on this?
Thanks
Edit:
BTW, The above Sub also only works if A1 consists of text such as "Aug-07". And it must be in format of regular Text, not a date (through Ctrl + 1). I could've just Left("A1", 1) probably instead of convering it to a Text cell then doing Left("A1", 3).
