+ Reply to Thread
Results 1 to 24 of 24

CTRL + F , in VBA code ?

  1. #1
    Forum Contributor
    Join Date
    03-28-2013
    Location
    singapore
    MS-Off Ver
    Excel 2010
    Posts
    157

    Post CTRL + F , in VBA code ?

    Hi , I'm new to VBA excel still learning.

    Problem:: In excel if u press CTRL+F , the find and replace window pop up rite.

    FOR EXample : you open, Find and replace --> click option---> set within: sheet , set search:rows , set Look in: Formulas--->then, in Find what : u enter some data and you click find all.

    Imagine,In the results ,you have many data... E.g 10 cells.
    Now i want that 5th cells or particular cells to copied and paste on to other sheet .



    How to do that automatically ???


    Excel 2010 file attachment below, pls reply
    Thank u
    Attached Files Attached Files
    Last edited by joh46k; 04-11-2013 at 03:49 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: CTRL + F , in VBA code ?

    Hi Joh

    the attachment would help to clarify exactly what we need...

    PS
    CTRL-f is find
    CTRL-H is find and replace
    Elegant Simplicity............. Not Always

  3. #3
    Forum Contributor
    Join Date
    03-28-2013
    Location
    singapore
    MS-Off Ver
    Excel 2010
    Posts
    157

    Re: CTRL + F , in VBA code ?

    I attached a Excel 2010 file #1

    pls take a look at . Thank u !!
    Last edited by joh46k; 04-11-2013 at 03:48 AM.

  4. #4
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: CTRL + F , in VBA code ?

    I couldn't open it due to "unreadable content"... Save it as an .xls (97-2003) file and try uploading again.

  5. #5
    Forum Contributor
    Join Date
    03-28-2013
    Location
    singapore
    MS-Off Ver
    Excel 2010
    Posts
    157

    Post Re: CTRL + F , in VBA code ?

    Ok, try this .xls (97-2003) file
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: CTRL + F , in VBA code ?

    so you want to find cells with a string of text on sheet 2 and transfer them to the sheet 1?

    Which column are we looking for the string in ?

  7. #7
    Forum Contributor
    Join Date
    03-28-2013
    Location
    singapore
    MS-Off Ver
    Excel 2010
    Posts
    157

    Re: CTRL + F , in VBA code ?

    @AndyLitch : yes ur rite

    On cost estimation check form sheet : H column called description. (H:25:H40)
    Last edited by joh46k; 04-11-2013 at 03:58 AM.

  8. #8
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: CTRL + F , in VBA code ?

    The "Flow Description" ?

  9. #9
    Forum Contributor
    Join Date
    03-28-2013
    Location
    singapore
    MS-Off Ver
    Excel 2010
    Posts
    157

    Re: CTRL + F , in VBA code ?

    @ AndyLitch:

    YES . "Flow Description " H:25:H40

    E.G: "On cost estimation checkform" sheet ,if u try copy H:25 and go to ALL sheet ---> and u find for the flow description. they show you 12 cells found...if i want 3rd cell from bottom.

    How to automatically using vba code ,copy that particular cells in that row and paste on to "cost estimation checkform sheet"
    H:8
    Last edited by joh46k; 04-11-2013 at 04:38 AM.

  10. #10
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: CTRL + F , in VBA code ?

    Look Description.xls

    Try that

    select a value in the dropdown
    all matching items are listed in the table below

  11. #11
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: CTRL + F , in VBA code ?

    Hi Joh46k,

    Can you share a sample workbook.. ? Thanks.


    [EDIT]: sorry, kept this winodw open for a longer duration and in - between you already uploaded a sample workbook and I can see Andy has already picked this up

    Regards,
    DILIPandey
    <click on below * if this helps>
    Last edited by dilipandey; 04-11-2013 at 05:42 AM. Reason: late reply :)
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  12. #12
    Forum Contributor
    Join Date
    03-28-2013
    Location
    singapore
    MS-Off Ver
    Excel 2010
    Posts
    157

    Re: CTRL + F , in VBA code ?

    @AndyLitch:
    Thanks but that's not what i expected !!

    Pls , see my post #9
    Last edited by joh46k; 04-11-2013 at 04:38 AM.

  13. #13
    Forum Contributor
    Join Date
    03-28-2013
    Location
    singapore
    MS-Off Ver
    Excel 2010
    Posts
    157

    Re: CTRL + F , in VBA code ?

    "Bump no response"

  14. #14
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: CTRL + F , in VBA code ?

    I was re-coding but Excel has now crashed on me...............
    so presumably you want to select a product type which will return a list of all the selected type.
    You then want to select one product only from the returned list and append that to the worksheet ??

  15. #15
    Forum Contributor
    Join Date
    03-28-2013
    Location
    singapore
    MS-Off Ver
    Excel 2010
    Posts
    157

    Re: CTRL + F , in VBA code ?

    the problem is if 4 cells it is ok..what if more than 100 cells ,selecting a items is same as Pressing ctrl + F

    I want use VBA because it saves time for user !!

  16. #16
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: CTRL + F , in VBA code ?

    I'm confused........ Do you want to put all the search finds into the table or just one ?

  17. #17
    Forum Contributor
    Join Date
    03-28-2013
    Location
    singapore
    MS-Off Ver
    Excel 2010
    Posts
    157

    Re: CTRL + F , in VBA code ?

    @AndyLitch:

    what i want to see is ,look for similar Previous week flow description on 'All' sheet that is same as in (H25:H32) on 'cost estimation checkform' sheet and paste that copied description on 'similar technology' table in checkform sheet.

    1)on 'cost estimation checkform' sheet , you can see two table right, one is (Similar technology SCO for new part ID cost comparison) and another is (New part ID Estimated Cost )

    2)Evey week, (New part ID Estimated Cost) table will be updated. so what i manually do is , i copy the flow description from (New part ID Estimated Cost) table and i go to 'All' sheet -> (ctrl+F) to find for similar flow description, if it is equal with previous week description then i need to copy that previous week ,flow description and their material number etc .

    3) then go to 'cost estimation checkform' sheet and paste the copied data on (Similar technology SCO for new part ID cost comparison) table.

    ...after that i need to do some cost estimate calculation based on two tables (I column)

    so all the steps above, i need to do automatically, when single 'update' button pressed !!

  18. #18
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: CTRL + F , in VBA code ?

    Why don't you just create a copy of the cost estimation sheet ?

  19. #19
    Forum Contributor
    Join Date
    03-28-2013
    Location
    singapore
    MS-Off Ver
    Excel 2010
    Posts
    157

    Re: CTRL + F , in VBA code ?

    @AndyLitch::: Actually what i need to do is delta vs reference calculation in k column by comparing thier cost estimate two tables !!

    Inorder do that, i need to copy flow description one by one from H:25 to H:32 ...and go to ALL sheet
    check the flow description are equal , if it is equal i need to select one of the previous equal value flow description . and copy that row's detail like their cost ,material , etc to the "cost estimation sheet" and paste to (H:8 to H:20).
    Last edited by joh46k; 04-11-2013 at 05:40 AM.

  20. #20
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: CTRL + F , in VBA code ?

    So you want to put product flow and cost into the cost estimation sheet and have all the product flow details returned from the database ??
    How do you know what cost to put in?

  21. #21
    Forum Contributor
    Join Date
    03-28-2013
    Location
    singapore
    MS-Off Ver
    Excel 2010
    Posts
    157

    Re: CTRL + F , in VBA code ?

    @
    AndyLitch::: yea, something like that!.. to answer your question

    How do you know what cost to put in?
    Ans:In "All" sheet ,column K,M,O,Q have Q1 ,Q2,Q3,Q4 which means quarter 1 ,quarter 2 etc . so each quarter have 3 month for e.g Q1 have Jan-march cost and quarter 2 have APR-JUN cost and etc. so in the attachment i given ,you need to focus Q4,(ORANGE COST & release date).

    If i want to rephrase that #20, i need to find previous weeks , flow description in "All" sheet which is equal as this week flow description given in (H:25 to H:40) in cost estimation sheet.
    after u find that previous weeks flow description in "All" sheet. copy their row detail and paste in to A8:J20 on (Similar technology table)

    once A8:J20 is filled , i can easily do my delta vs reference by comparing their cost estimate column (I column)

    so in the attachment i already manually finished it. my question is how to do that automatically so you don't need to press ctrl+f every time in "All" sheet to find it ! instead in single button click u can do that job.
    Last edited by joh46k; 04-11-2013 at 11:58 PM.

  22. #22
    Forum Contributor
    Join Date
    03-28-2013
    Location
    singapore
    MS-Off Ver
    Excel 2010
    Posts
    157

    Re: CTRL + F , in VBA code ?

    "Bump no response"
    Last edited by joh46k; 04-14-2013 at 09:03 PM.

  23. #23
    Forum Contributor
    Join Date
    03-28-2013
    Location
    singapore
    MS-Off Ver
    Excel 2010
    Posts
    157

    Re: CTRL + F , in VBA code ?

    @AndyLitch: "Bump no response"
    Last edited by joh46k; 04-15-2013 at 03:57 AM.

  24. #24
    Forum Contributor
    Join Date
    03-28-2013
    Location
    singapore
    MS-Off Ver
    Excel 2010
    Posts
    157

    Re: CTRL + F , in VBA code ?

    "Bump no response"

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1