Paul-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
On Saturday, November 5, 2016 1:52 AM, amar takale <amartak...@gmail.com>
wrote:
Dear Paul Sir,
Good Morning.
Thankyou so much for your code, for the effort and time dedicated.And your
explanation is too good but bcos of less understanding VBA coding I cant get
all information but any way i sort out my issue.Currently i Use Excel 2003 &
future planning to work on excel 2007. So then I can use both file as per my
requirement?
I have also some question here
1) I want change data validation as name range not put single name by comm,then
i will use name range & add many place in name range only.
I'm not sure what you mean by this.I THINK you're saying that you want the
Range Name for the data to be "comm".then, you can add more data to the range
and the macros will use the name.I have done this in the file, but called the
range "Data".You can define your own name and change each occurrence of "Data"
to "comm".
2) can I put countif formula in sheet (F2) with cell match C2 & D2.Actually
requirement is that if place is match & email ID match then count how many
email ID there.Example like "in 'Mumbai' how may 'Hotmail ID' there are & count
it" also wildcard match.
In Excel 2003, you cannot use multiple columns in a single countIF().What you
COULD do, however, is to combine the two columns (C and D) into column E
(=C1&D1),then use a countif with wildcards like:=COUNTIF(E:E,"*Mumbai*hotmail*")
If you wanted to use the search cells, you could use:
=COUNTIF(E:E,C2 &"*" & D2 &"*")
or=COUNTIF(E:E,SearchPlace &"*" & SearchString &"*")
In Excel2007, there is a function:=COUNTIFS()that allows multiple criteria and
ranges.
Thanks again sir
Regards
Amar
On Fri, Nov 4, 2016 at 5:46 PM, Paul Schreiner <schreiner_p...@att.net> wrote:
See answers below:
On Friday, November 4, 2016 2:00 AM, amar takale <amartak...@gmail.com>
wrote:
Dear Paul Sir,
This is fabulous work!
I think this must be flashing on the front page! :D. And I am Extremely sorry
for so late reply bcos of diwali vacation. I am VBA learner so only basic
knowledge of VBA coding.
I have some Question here:
1) Can I use this code in excel 2007 after giving change specific range as per
in your code in excel 2007.
This code was written in Excel 2010. Since I didn't know which version you
were using, I saved it as Excel97-2003, which could be opened in whatever
version you were using.As you can see, I wrote it in Excel 2010, saved it as
Excel97, so yes, it should work perfectly in Excel2007.In fact, the fact that
you've tested it means that it DOES!Your Excel2007 opened the Excel97 file and
converted it internally.Keep in mind that in Excel2007, to preserve the macros,
you must save it as .xlsm or .xlsb.Personally, I prefer .xlsb.It loads and runs
faster than .xlsm for large data sets.
2) what is relation E column with code ( Can I delete this column,then code
work proper?) and what is relation "flag" word with code ( Can I delete this
word)
You wanted to be able to enter multiple words in the "Search any string"
box.The problem is: The Autofilter only allows 2 "search strings".So, I set up
a "helper" column to hold a "display flag".In the macro, I loop through all
rows and if the value in the "EmailID" column contains all search strings, it
sets the "Flag" column to "X"After all rows are processed, this column is
filtered to only show non-blank cells.
If you prefer, we can change this macro to remove background color from the
cells in column D, then set the color to something specific when all strings
are matched, then filter on that color.
3) in module1 code I found there are show specific word (Criteria1:="Chenai" &
"=*hotmail*") why it show only specific criteria word show.Code work for all
country & email ID na? I check it & find it work for all proper way as per my
requiment.
Albert Einstein was quoted as saying: “Never memorize something that you can
look up.”I have no idea if he actually said that, but I've always followed that
advice.I don't have enough brain cells to waste trying to remember the syntax
of every Method and Procedure in VBA.Instead, I just have to know where to look
it up.I often use Excel's macro recorder to collect the commands necessary to
perform the steps I wish to accomplish.In this case, I recorded the steps of
clearing the filters (Macro2 had: "Activesheet.ShowAllData")and Macro1 had the
steps to set the filter for column 3 to "Chenai" and column 4 to
"Hotmail"notice that "Hotmail" had (*) in front and behind?That is what was
necessary for when I selected the filter to "contain" "Hotmail".where Chenai
was the complete cell value.
So, since in your final worksheet, the data may have more than 50 lines, I
replaced the recorded "$A$4:$D$51" with the Named Range "Data".And "Chenai"
became the value of the Named Range "SearchPlace": Criteria1:=
ActiveSheet.Range(" SearchPlace").Value I normally delete these to avoid
confusion. (Oops!)
Thanks ones again Paul sir
Attached, I've made a couple of corrections (I'd forgotten to reset the flags
before re-filtering!)I also used "Color" instead of a "Helper Column".
Let me know if you have further questions.
RegardsAmar
On Fri, Oct 28, 2016 at 5:11 PM, Paul Schreiner <schreiner_p...@att.net> wrote:
OK, here's something to look at.
I'm not sure if you'll need help implementing it, but here's how it works:
There are several "Named Ranges".For instance, your entire list is called
"Data" in order to manage the Filters.The cell for "place" is called
"SearchPlace" and the cell for strings is called "SearchString".You'll need
these Named Ranges to implement this code in your workbook.
I created a macro to update a pull-down list of search "Places".Selecting from
this list will filter your list by this "Place".(only one place can be selected)
The list updates with a "Calculate" event.
Using a "Change" event, I convert the search string to a word array (space
delimited).If only one word is present, column "D" is filtered for records that
contain this word.
If multiple words are found, then the macro loops through records and updates
the "Flag"column (E) if all words are found IN COLUMN D!Then filters the data
on column "E".
Take a look and see if it works the way you envisioned.
If not, let me know.
also, let me know if you need help implementing it in your own workbook.
Paul------------------------------ -----------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
------------------------------ -----------
On Wednesday, October 26, 2016 12:23 AM, amar takale <amartak...@gmail.com>
wrote:
Dear Hilary sir pls look into this matter.
Also from Paul sir expect lot.
On Mon, Oct 24, 2016 at 5:42 PM, amar takale <amartak...@gmail.com> wrote:
Dear Sir
Thanks for reply.but my requirement not this way (And I am use excel 2003). I
want to search any string from specific cell (D2) then data filter on that
basis like wildcard criteria.
My search criteria like hotmail,gmail,water,emirates, net,.com,.ae ,wave etc
from D2 & then filter data
Regards
Amar
On Mon, Oct 24, 2016 at 4:18 PM, Hilary Lomotey <resp...@gmail.com> wrote:
See attached, HTH. Cheers
On Mon, Oct 24, 2016 at 5:35 AM, amar takale <amartak...@gmail.com> wrote:
Dear Friends
any one help me on this issue
On Fri, Oct 21, 2016 at 12:09 PM, amar takale <amartak...@gmail.com> wrote:
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discu ssexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscribe@googl egroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/grou p/excel-macros.
For more options, visit https://groups.google.com/d/op tout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discu ssexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscribe@googl egroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/grou p/excel-macros.
For more options, visit https://groups.google.com/d/op tout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discu ssexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscribe@googl egroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/grou p/excel-macros.
For more options, visit https://groups.google.com/d/op tout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/ discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscribe@ googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/ group/excel-macros.
For more options, visit https://groups.google.com/d/ optout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/ discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscribe@ googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/ group/excel-macros.
For more options, visit https://groups.google.com/d/ optout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/ discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscribe@ googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/ group/excel-macros.
For more options, visit https://groups.google.com/d/ optout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/ discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscribe@ googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/ group/excel-macros.
For more options, visit https://groups.google.com/d/ optout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
