Select Case

R

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
 
S

Simon Lloyd

What said:
My below code is not giving the result to i excpect, can i know where i
th
mistake

I have four columns
I have created two variables for the fourth column one is a3 an
CurMth, i
select case MyDate if a3 isequal to MyDate then the result in fift
colum
need to Ye Code
-------------------
Sub Results(
Dim CurMth As Strin
Dim PrvMth As Strin


RowCount =
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 & a
MsgBox a


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


Select Case Ag
Case Is < 60
Select Case Frequenc
Case "Monthly"
Select Case MyDat
Case CurMth = a3
Results = "Yes

End Selec

End Selec
End Selec

Range("E" & RowCount) = Result

RowCount = RowCount +
Loo

End Su
-------------------

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com
 
R

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
 
S

Simon Lloyd

Please just explain what it is you want to do,please give the seperat
criteria for adding YES to the category, it's a little difficult t
understand what you are trying to achieve
Ranjit said:
my result need to Yes, example shown belo
Aging Frequency Age MyDate Categor
1-30 Monthly 50 Jan-09 Ye
61-90 Monthly 40 Dec-08 Ye







Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1
(http://www.thecodecage.com/forumz/showthread.php?t=54192

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com
 
D

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
 
R

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

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)
Ranjit kurian said:
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.

Simon Lloyd said:
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
 
D

Dana DeLouis

Ranjit said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top