Consulting

Results 1 to 10 of 10

Thread: i want VBA to display values greater than or equal to the given number

  1. #1
    VBAX Regular
    Joined
    Jun 2019
    Posts
    21
    Location

    i want VBA to display values greater than or equal to the given number

    I tried to create 2 text box from ActiveX control, where first box will search given text value for me from the range, and i want to put a certain no to my 2nd text box which required to concatenate with the first text boxes value and will display me the data equal or greater than the value provide in the 2nd box.

    example:-

    In Sheet1, B column is having my data like - "CORE JAVA (73),CORE JAVA (3),
    AL-Test (20)" etc. all numeric values are in ().
    in first text box i put the criteria for filter =textbox1.value & "*" for related search Now can any one help me what to do or how to code the second box so that if i put "10" or any other no. in the second box so that it will display the data along with the >=data(10) from B column.

    Attaching the File for reference.
    Attached Files Attached Files

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    See attached.
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    VBAX Regular
    Joined
    Jun 2019
    Posts
    21
    Location

    Hi Please help me with this

    Quote Originally Posted by p45cal View Post
    See attached.

    As you scripted it is not coming for my this scenario..
    Refer attachment

    is there any other method so that i can apply it over my every possible aspect, can't we define any other methods for box2 only which can help to find all related search with similar or greater than the given value?

    Please help
    Attached Files Attached Files

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Your second file is considerably different from your first, especially regarding there being multiple skills and experience in single cells.
    Hiding/unhiding rows with code doesn't work well with autofilters; to get unambiguous results it needs to be autofilter only, or something else without autofilter.


    Quote Originally Posted by supraman View Post
    is there any other method so that i can apply it over my every possible aspect
    The main problem lies with the unconventional data layout. To cater easily for many aspects requires a radical change of data layout. Having said that, attached has a bespoke solution for your new scenario.
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    VBAX Regular
    Joined
    Jun 2019
    Posts
    21
    Location
    Quote Originally Posted by p45cal View Post
    Your second file is considerably different from your first, especially regarding there being multiple skills and experience in single cells.
    Hiding/unhiding rows with code doesn't work well with autofilters; to get unambiguous results it needs to be autofilter only, or something else without autofilter.


    The main problem lies with the unconventional data layout. To cater easily for many aspects requires a radical change of data layout. Having said that, attached has a bespoke solution for your new scenario.
    Hay Man,
    Greetings of the day.

    The script is absolutely fine but as per my original data I need to replace SL no with Emp Id and In column D there will be many blanks for Skills.

    let me tell you i have another script with the file which help me to clear all the old data from A4:F4 till lastrow and paste the new data along with Headers (A4:F4) from another file which can increase the row numbers only. Now problem is when i tried to run the script on my original Data where data range in column is same but volume can varies (20K rows approx) giving me a Run time error on line "aa = UCase(Left(xx(j), lastOpenBracket - 1))" in the script with a error msg "invalid procedure call or argument".

    Can you please help me on this.
    Or is it possible to create some code which can marge TB1 and TB2 value and provide a search result combined, so that if i put "Core java" in TB1 search until i am not giving any numeric value in TB2 it will not work, similarly if i change Tb1 search from "Core java" to some other name for the same value of TB2 or vise versa change in TB2 Value, it need to display the TB1 search with the similar or greater Value of TB2 from D column.

    If possible can you please explain me the entire code as i haven't understood few lines from the script. i have attached my file where i have another script for clear and paste the required data, Original file is more than 10 MB hence unable to attach that file, you can find the problem every time once you replace old data with a new one.


    My deadline for the task is about to end. if possible can you please short this issue at the earliest.Or can we connect over a call or sharing the screen so that i can narrate you my need on real time.


    Support required badly.
    Subhankar
    +91-7618788845.
    Awaiting for your response.
    Attached Files Attached Files
    Last edited by supraman; 06-24-2019 at 02:17 AM.

  6. #6
    VBAX Regular
    Joined
    Jun 2019
    Posts
    21
    Location
    Quote Originally Posted by p45cal View Post
    Your second file is considerably different from your first, especially regarding there being multiple skills and experience in single cells.
    Hiding/unhiding rows with code doesn't work well with autofilters; to get unambiguous results it needs to be autofilter only, or something else without autofilter.


    The main problem lies with the unconventional data layout. To cater easily for many aspects requires a radical change of data layout. Having said that, attached has a bespoke solution for your new scenario.

    need your help please

  7. #7
    VBAX Regular
    Joined
    Jun 2019
    Posts
    21
    Location
    hello sir,

    Can you please help me on this.

  8. #8
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    The attached has a different solution because using the previous technique was taking far too long to produce reliable results - each time a text box was changed it took an age to update the sheet. This is because there are too many data in column D.
    Instead, there is a macro blah4 which transforms the data in Sheet1 and puts it into Sheet3 (you could filter this new table in a similar way to how you did it it on Sheet1 but I used a pivot table instead which is fast and flexible).
    The pivot table is on Sheet3 around cell K8. Above it are some text boxes that work in a similar way to your text boxes but they work on the pivot table instead. Be aware that the text boxes only affect the 4 fields they relate to and do not affect any other filters you may have applied to the other fields.
    If you choose to update Sheet1 with new data, you will need to delete the data on Sheet3 only from row 2 downwards, then run blah4, then update the extent of the range of the pivot table source data.
    You will only need to run blah4 once when new data are placed in Sheet1
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  9. #9
    VBAX Regular
    Joined
    Jun 2019
    Posts
    21
    Location

    please complete

    I am in a beginner level of coding hence unable to understand the BLAh logic, sorry sound wired. but literally its up to my head.

    here what i want from attached file which was bit modified using some option button. this is absolutely my logic to control the data basis of Option button.
    first two option button is for filtering "Virtual" or "Existing" from A column and second set of option button with white shade is for location from column G, just to modified the search for virtual or existing basis along with onsite or offshore. now when i am trying to search the skill from box 1 along with box 2 value, or Box 3 or Box 4 result showing under data sheet is not following the option button filter condition. column A and H showing data as per text box search criteria, however i tried a lot to code like Text box will follow only option button filter.

    My code is for the search :- first select "Virtual" or "Existing" second select "Onsite" or "Offshore" and then i will put the value or data in text box and text box will show me the data accordingly. but unfortunately i think module 2 or may be module 3 need to do something which i am not aware of. also when providing value in box 3 it is not giving result as per the box1 and box 2 search.

    can you please help on this. i know i am asking so much but i just invented the easiest way to provide the data where no one will be confused.

    Please help with attach file

    Thanks,
    Supraman
    Attached Files Attached Files

  10. #10
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    As I said before:
    Quote Originally Posted by p45cal View Post
    Hiding/unhiding rows with code doesn't work well with autofilters; to get unambiguous results it needs to be autofilter only, or something else without autofilter.
    You're discovering this for yourself.
    I cannot adjust your code to make it work.
    The macro blah is only to re-arrange the data onto Sheet3. You've introduced new fields. I've adjusted blah to cope with your new fields. This rearranged data is the source for the pivot table. I've also adjusted that pivot table (on sheet3 near cell M9), added a few slicers and used 4 textboxes:
    Location - enter part of a location and it will filter the list accordingly
    Grade - enter part of the name of the group and it will filter. You can also enter 1, 2 or 3 to filter for your own grade groups.
    Skill - enter part of a skill name - it will filter immediately.
    Skill level - enter a number, it will show that level and above.

    Slicers - Click on what you want to filter for.

    There is no reset button, just manually clear the textboxes, click the top left x in the slicers.
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •