Select Case

[Ranjit kurian]

My below code is not giving the result to i excpect, can i know where is the
mistake.

I have four columns.
I have created two variables for the fourth column one is a3 and CurMth, in
select case MyDate if a3 isequal to MyDate then the result in fifth column
need to Yes

Sub Results()
Dim CurMth As String
Dim PrvMth As String


RowCount = 1
Do While Range("A" & RowCount) <> ""
Aging = Range("A" & RowCount)
Frequency = Range("B" & RowCount)
Age = Range("C" & RowCount)
MyDate = Range("D" & RowCount)

a = ActiveCell.Offset(0, 0).Range("D" & RowCount)
a1 = Right(a, 2)
a2 = Left(a, 2)
a3 = a2 & a1
MsgBox a3


CurMth = Format(Date, "mmyy")
PrvMth = Format(DateSerial(Year(Date), Month(Date) - 1, 1), "mmyy")
MsgBox CurMth & vbLf & PrvMth


Select Case Age
Case Is < 60:
Select Case Frequency
Case "Monthly":
Select Case MyDate
Case CurMth = a3:
Results = "Yes"

End Select

End Select
End Select

Range("E" & RowCount) = Results

RowCount = RowCount + 1
Loop

End Sub

 

[Ranjit kurian]

my result need to Yes, example shown below
Aging Frequency Age MyDate Category
1-30 Monthly 50 Jan-09 Yes
61-90 Monthly 40 Dec-08 Yes

 

[Dana DeLouis]

My below code is not giving the result to i excpect, can i know where
is the mistake.

Hi. I would indent the code. Perhaps something like this.
I believe this could be written a little differently.

I am just guessing here on this part...

Select Case MyDate
Case CurMth = a3:

I am guessing that CurMth = a3 returns either True or False.
I think you are comparing MyDate with True/False.
This could be a logic error.
Just guessing of course.


Select Case Age
Case Is < 60:
Select Case Frequency
Case "Monthly":
Select Case MyDate
Case CurMth = a3:
Results = "Yes"
End Select
End Select
End Select

HTH
Dana DeLouis

 

[Ranjit kurian]

i have five columns(Aging,Frequency, Age, MyDate Category)

If the Frequency is Monthly and If the Age is less than 60 and If the
MyDate(iam using 2003 excel, i manually changed the format of column to
mmm/yy, but the macro code read the date as mm/dd/yyyy, so i have used right
and left function to take only month and year separately [variable used "a3 =
a2 & a1"] and compare with the variable ["CurMth = Format(Date, "mmyy")"])
the result in Category column need to Yes ELSE NO.

i have stuckup in MyDate column , i clearly don't understand how to compare
the two variables in select case.

 

[Ranjit kurian]

Yes, iam compare the MyDate column, can you advise me the way to compare

 

[Don Guillett]

It may be easier for you to send your workbook to my address below along
with a clear explanation, snippets of these emails copied to an inserted
sheet, and before/after example(s)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

i have five columns(Aging,Frequency, Age, MyDate Category)

If the Frequency is Monthly and If the Age is less than 60 and If the
MyDate(iam using 2003 excel, i manually changed the format of column to
mmm/yy, but the macro code read the date as mm/dd/yyyy, so i have used
right
and left function to take only month and year separately [variable used
"a3 =
a2 & a1"] and compare with the variable ["CurMth = Format(Date, "mmyy")"])
the result in Category column need to Yes ELSE NO.

i have stuckup in MyDate column , i clearly don't understand how to
compare
the two variables in select case.

Please just explain what it is you want to do,please give the seperate
criteria for adding YES to the category, it's a little difficult to
understand what you are trying to achieve!



--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.thecodecage.com/forumz/member.php?userid=1
View this thread:
http://www.thecodecage.com/forumz/showthread.php?t=54192

 

[Dana DeLouis]

Yes, iam compare the MyDate column, can you advise me the way to compare

Hi. Just guessing, but maybe this general outline...

Select Case Age
Case Is < 60:
Select Case Frequency
Case "Monthly":
Select Case MyDate
Case a3
Results = "Yes"
End Select
Case "Yearly"
'whatever
End Select
Case Is >= 60
'whatever
End Select

Since I don't see any other options, perhaps this general idea...

If MyDate = A3 then
result = "yes"
else
result = "no"
end if

Again, I believe one of the logic errors was comparing
MyDate to (CurMth = a3) which ()is either true/false

'HTH
Dana DeLouis