Select Case
 

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

Select Case
 

What is the expected result?Ranjit kurian;196646 Wrote:
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

Code:
--------------------

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

--------------------



--
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/sh...ad.php?t=54192

Select Case
 

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




"Simon Lloyd" wrote:


What is the expected result?Ranjit kurian;196646 Wrote:
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

Code:
--------------------

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

--------------------



--
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/sh...ad.php?t=54192

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!
Ranjit kurian;196826 Wrote:
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




"Simon Lloyd" wrote:


What is the expected result?Ranjit kurian;196646 Wrote:
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

Code:
--------------------

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

--------------------



--
Simon Lloyd

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

------------------------------------------------------------------------
Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon
Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread: 'Select Case - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...ad.php?t=54192)




--
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/sh...ad.php?t=54192

Select Case
 

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 wrote:
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

Select Case
 

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" wrote:


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!
Ranjit kurian;196826 Wrote:
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




"Simon Lloyd" wrote:


What is the expected result?Ranjit kurian;196646 Wrote:
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

Code:
--------------------

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

--------------------



--
Simon Lloyd

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

------------------------------------------------------------------------
Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon
Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread: 'Select Case - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...ad.php?t=54192)




--
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/sh...ad.php?t=54192

Select Case
 

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

"Dana DeLouis" wrote:

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 wrote:
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

Select Case
 

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

"Ranjit kurian" wrote in message
...
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" wrote:


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!
Ranjit kurian;196826 Wrote:
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




"Simon Lloyd" wrote:


What is the expected result?Ranjit kurian;196646 Wrote:
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

Code:
--------------------

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

--------------------



--
Simon Lloyd

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

------------------------------------------------------------------------
Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon
Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread: 'Select Case - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...ad.php?t=54192)




--
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/sh...ad.php?t=54192

Select Case
 

Ranjit kurian wrote:
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



"Dana DeLouis" wrote:

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 wrote:
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