Dear Paul Sir,
Good Morning.
Thank you 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.
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.
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.*
>
> Regards
> Amar
>
>
>
> 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)
>
>
> [image: Inline image]
> 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 <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@googl egroups.com
> <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/grou p/excel-macros
> <https://groups.google.com/group/excel-macros>.
> For more options, visit https://groups.google.com/d/op tout
> <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/discu
> ssexcel <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@googl egroups.com
> <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/grou p/excel-macros
> <https://groups.google.com/group/excel-macros>.
> For more options, visit https://groups.google.com/d/op tout
> <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 <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
> <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
> <https://groups.google.com/group/excel-macros>.
> For more options, visit https://groups.google.com/d/ optout
> <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 <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
> <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
> <https://groups.google.com/group/excel-macros>.
> For more options, visit https://groups.google.com/d/ optout
> <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.
>
--
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.
