Automatically display sentence.

[Rao Ratan Singh]

Sir,
First I want to thank Mr Max who responded me and tried to solve my problem.
I m also very sorry that I have not good knowledge of English. If someone can
understand my problem and can solve this I will be very grateful. I have a
worksheet which have columns in this manner –


A B C D
Concession Form Received

6 List of C or D Form Received.
7 Form Form No. Date Amount
8 C Form
9 D Form
10 C Form
11 C Form
12 D Form
13 D Form
14 Full Tax
15 C Form

I want to do that a sentence “List of C Form Received” or List of C Form
Due”, or List of [ blank ] Form” automatically display in a6 in A6. only in
that case when in b8 in front of Form; Form No is Filled.

Otherwise it should be returned blank.

This A6 content sentence should be change with Auto Filter filtering with
specific C or D Form selection. Like when I select blank and C Form it should
be “List of C Form not Recd”, when I selct not blank and C Form it should be
display “List of C Form Recd.”

Regards
RRS

[Max]

Perhaps you might want to try this UDF from a previous post
by Tom Ogilvy in microsoft.public.excel.programming
[Subject: Re: Read AutoFilter Criteria]
UDF = User defined function

Tom's UDF will display the autofilter criteria selected in a cell

A revised sample with Tom Ogilvy's UDF implemented is available at:
http://www.savefile.com/files/4473648
Display_AutoComposed_Sentence_V2_Rao_newusers.xls

To implement the UDF:
Press Alt+F11 to go to VBE
Click Insert > Module
Copy and paste the UDF* into the white space on the right
*everything within the dotted lines ("begin vba" to "end vba")
Alt+Q to get back to Excel

Then in Excel, we could use Tom's UDF
by putting in a cell, say B3: =showfilter(A:A)
B3 will return the filter criteria effected in col A
If you choose "C Form" from the autofilter droplist in A7,
B3 will return: "=C Form"

Since we want to auto-compose the sentence in A6 by capture the autofilter
criteria effected in cols A and B, we could try in A6 something like:
="List of "&TRIM(SUBSTITUTE(showfilter(A:A),"=","")&"
"&SUBSTITUTE(showfilter(B:B),"=",""))&" Received."

You'd need to tinker around with all the possible autofilter criteria
selected in A7 and B7 in your *actual* file, and see the returns from Tom's
UDF. Then refine the formula in A6 further, possibly by using more nested
SUBSTITUTE(...) so that each combo-selection will give the required
"sentence" in A6.

-- begin vba --
Public Function ShowFilter(rng As Range)
Dim filt As Filter
Dim sCrit1 As String
Dim sCrit2 As String
Dim sop As String
Dim lngOp As Long
Dim lngOff As Long
Dim frng As Range
Dim sh As Worksheet
Set sh = rng.Parent
If sh.FilterMode = False Then
ShowFilter = "No Active Filter"
Exit Function
End If
Set frng = sh.AutoFilter.Range

If Intersect(rng.EntireColumn, frng) Is Nothing Then
ShowFilter = CVErr(xlErrRef)
Else
lngOff = rng.Column - frng.Columns(1).Column + 1
If Not sh.AutoFilter.Filters(lngOff).On Then
ShowFilter = "No Conditions"
Else
Set filt = sh.AutoFilter.Filters(lngOff)
On Error Resume Next
sCrit1 = filt.Criteria1
sCrit2 = filt.Criteria2
lngOp = filt.Operator
If lngOp = xlAnd Then
sop = " And "
ElseIf lngOp = xlOr Then
sop = " or "
Else
sop = ""
End If
ShowFilter = sCrit1 & sop & sCrit2
End If
End If
End Function
-- end vba --


Hope this takes you a little closer to your goal ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Rao Ratan Singh" <[email protected]> wrote in message
news:[email protected]...
> Sir,
> First I want to thank Mr Max who responded me and tried to solve my
problem.
> I m also very sorry that I have not good knowledge of English. If someone
can
> understand my problem and can solve this I will be very grateful. I have a
> worksheet which have columns in this manner -
>
>
> A B C D
> Concession Form Received
>
> 6 List of C or D Form Received.
> 7 Form Form No. Date Amount
> 8 C Form
> 9 D Form
> 10 C Form
> 11 C Form
> 12 D Form
> 13 D Form
> 14 Full Tax
> 15 C Form
>
> I want to do that a sentence "List of C Form Received" or List of C Form
> Due", or List of [ blank ] Form" automatically display in a6 in A6. only
in
> that case when in b8 in front of Form; Form No is Filled.
>
> Otherwise it should be returned blank.
>
> This A6 content sentence should be change with Auto Filter filtering with
> specific C or D Form selection. Like when I select blank and C Form it
should
> be "List of C Form not Recd", when I selct not blank and C Form it should
be
> display "List of C Form Recd."
>
> Regards
> RRS

[Max]

Perhaps you might want to try this UDF from a previous post
by Tom Ogilvy in microsoft.public.excel.programming
[Subject: Re: Read AutoFilter Criteria]
UDF = User defined function

Tom's UDF will display the autofilter criteria selected in a cell

A revised sample with Tom Ogilvy's UDF implemented is available at:
http://www.savefile.com/files/4473648
Display_AutoComposed_Sentence_V2_Rao_newusers.xls

To implement the UDF:
Press Alt+F11 to go to VBE
Click Insert > Module
Copy and paste the UDF* into the white space on the right
*everything within the dotted lines ("begin vba" to "end vba")
Alt+Q to get back to Excel

Then in Excel, we could use Tom's UDF
by putting in a cell, say B3: =showfilter(A:A)
B3 will return the filter criteria effected in col A
If you choose "C Form" from the autofilter droplist in A7,
B3 will return: "=C Form"

Since we want to auto-compose the sentence in A6 by capture the autofilter
criteria effected in cols A and B, we could try in A6 something like:
="List of "&TRIM(SUBSTITUTE(showfilter(A:A),"=","")&"
"&SUBSTITUTE(showfilter(B:B),"=",""))&" Received."

You'd need to tinker around with all the possible autofilter criteria
selected in A7 and B7 in your *actual* file, and see the returns from Tom's
UDF. Then refine the formula in A6 further, possibly by using more nested
SUBSTITUTE(...) so that each combo-selection will give the required
"sentence" in A6.

-- begin vba --
Public Function ShowFilter(rng As Range)
Dim filt As Filter
Dim sCrit1 As String
Dim sCrit2 As String
Dim sop As String
Dim lngOp As Long
Dim lngOff As Long
Dim frng As Range
Dim sh As Worksheet
Set sh = rng.Parent
If sh.FilterMode = False Then
ShowFilter = "No Active Filter"
Exit Function
End If
Set frng = sh.AutoFilter.Range

If Intersect(rng.EntireColumn, frng) Is Nothing Then
ShowFilter = CVErr(xlErrRef)
Else
lngOff = rng.Column - frng.Columns(1).Column + 1
If Not sh.AutoFilter.Filters(lngOff).On Then
ShowFilter = "No Conditions"
Else
Set filt = sh.AutoFilter.Filters(lngOff)
On Error Resume Next
sCrit1 = filt.Criteria1
sCrit2 = filt.Criteria2
lngOp = filt.Operator
If lngOp = xlAnd Then
sop = " And "
ElseIf lngOp = xlOr Then
sop = " or "
Else
sop = ""
End If
ShowFilter = sCrit1 & sop & sCrit2
End If
End If
End Function
-- end vba --


Hope this takes you a little closer to your goal ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Rao Ratan Singh" <[email protected]> wrote in message
news:[email protected]...
> Sir,
> First I want to thank Mr Max who responded me and tried to solve my
problem.
> I m also very sorry that I have not good knowledge of English. If someone
can
> understand my problem and can solve this I will be very grateful. I have a
> worksheet which have columns in this manner -
>
>
> A B C D
> Concession Form Received
>
> 6 List of C or D Form Received.
> 7 Form Form No. Date Amount
> 8 C Form
> 9 D Form
> 10 C Form
> 11 C Form
> 12 D Form
> 13 D Form
> 14 Full Tax
> 15 C Form
>
> I want to do that a sentence "List of C Form Received" or List of C Form
> Due", or List of [ blank ] Form" automatically display in a6 in A6. only
in
> that case when in b8 in front of Form; Form No is Filled.
>
> Otherwise it should be returned blank.
>
> This A6 content sentence should be change with Auto Filter filtering with
> specific C or D Form selection. Like when I select blank and C Form it
should
> be "List of C Form not Recd", when I selct not blank and C Form it should
be
> display "List of C Form Recd."
>
> Regards
> RRS