+ Reply to Thread
Results 1 to 26 of 26

HELP PLEASE : How to make macros to search a word in the entire sheet & c/p the value

  1. #1
    Registered User
    Join Date
    08-20-2013
    Location
    HK
    MS-Off Ver
    Excel 2003
    Posts
    16

    HELP PLEASE : How to make macros to search a word in the entire sheet & c/p the value

    Hi everyone!

    I'm new with macro and VBA in excel and i hope you could help me on how to create a macro. I would like to find a certain text (e.g. Total Profit) in Column G(let say in the entire sheet) and copy the value next to it which is in the next two column and paste it L1 and M1.
    Please see attached file

    For example:
    I would like to search for the word "Total NT" in the entire sheet and copy the numbers in the next two column. In that example, "Total NT" is located in G31 and I would like to copy the value of 11 and 15 and paste it in column L1 and M1.
    As well as to search the word "Total Assets" (located in B31) and copy the value of of 999 and 2222 and wanted to paste the value at L3 and M3. and so on and so forth...

    By the way, my worksheet has datas from A1:J200


    Looking forward to hear anyone from you. Thanks!
    Attached Files Attached Files
    Last edited by kinabuch0902; 08-20-2013 at 01:22 PM.

  2. #2
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: HELP PLEASE : How to make macros to search a word in the entire sheet & c/p the valu

    in your sheets that searching value have duplicates as well.

    after looking around your sheet, what i suggest is if we search value only column B and G , what u say

    one more thing, u want the value only for that names whatever mention in column K right
    Thanks - Naveed
    -----------------------------
    If the suggestion helps you, then Click * to Add Reputation
    To Attach File: Go Advanced>>Manage Attachments>>Add Files (In Top Right Corner)>>SelectFiles>>.........Locate Your File(s)>>Upload Files>>Done (In Bottom Right)
    1. Use [code] code tags [\code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    2. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

  3. #3
    Registered User
    Join Date
    08-20-2013
    Location
    HK
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: HELP PLEASE : How to make macros to search a word in the entire sheet & c/p the valu

    Quote Originally Posted by Naveed Raza View Post
    in your sheets that searching value have duplicates as well.

    after looking around your sheet, what i suggest is if we search value only column B and G , what u say

    one more thing, u want the value only for that names whatever mention in column K right


    Yes the search word are in Column B and G.
    I want to search the words that are written in Column K. (Total NT, Total SM, Total Assets, etc.....)

  4. #4
    Registered User
    Join Date
    08-20-2013
    Location
    HK
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: HELP PLEASE : How to make macros to search a word in the entire sheet & c/p the valu

    Anyone here? Is it possible to create macro regarding on my concern above?

  5. #5
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: HELP PLEASE : How to make macros to search a word in the entire sheet & c/p the valu

    See this attached file and let me know is it what ur looking for.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    08-20-2013
    Location
    HK
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: HELP PLEASE : How to make macros to search a word in the entire sheet & c/p the valu

    can you tell me what did you do?
    Did you used vba code?
    if so, can you post here the code?

    My aplogogy, i'm totally a newbie in regards with marco in excel.

    Thanks in advance

  7. #7
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: HELP PLEASE : How to make macros to search a word in the entire sheet & c/p the valu

    no i just use the function have u not seen in the cells of column L and M

  8. #8
    Registered User
    Join Date
    08-20-2013
    Location
    HK
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: HELP PLEASE : How to make macros to search a word in the entire sheet & c/p the valu

    can we not apply in macro? actually i have few worksheets here that has about 2000 rows.

  9. #9
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: HELP PLEASE : How to make macros to search a word in the entire sheet & c/p the valu

    Hmm...., exactly where u want the output in same sheet at column L and M based on Column K only ?

  10. #10
    Registered User
    Join Date
    08-20-2013
    Location
    HK
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: HELP PLEASE : How to make macros to search a word in the entire sheet & c/p the valu

    Yeah the output value will be in the same sheet and can be pasted in Col L and M.
    I need to search and copy/paste the values what is written in column K.

    You got my point?

  11. #11
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: HELP PLEASE : How to make macros to search a word in the entire sheet & c/p the valu

    hey nothing will happen u want macro code for this one .

    if rows contain morethan a lack also that function will work just try out.....

    i don't think u required macro for this simple one..

  12. #12
    Registered User
    Join Date
    08-20-2013
    Location
    HK
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: HELP PLEASE : How to make macros to search a word in the entire sheet & c/p the valu

    okay. Thanks to you
    But I really need a macro so i can apply to my other worksheets.

  13. #13
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: HELP PLEASE : How to make macros to search a word in the entire sheet & c/p the valu

    just copy that function and paste it and drag it...

    please don't forget mark this Thread as SOLVED and click on ADD REPUTATION and give your valuable comments

  14. #14
    Registered User
    Join Date
    08-20-2013
    Location
    HK
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: HELP PLEASE : How to make macros to search a word in the entire sheet & c/p the valu

    i tried to copy and paste the function and drag it and it think there were errors.
    error - Circular Reference warning

  15. #15
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: HELP PLEASE : How to make macros to search a word in the entire sheet & c/p the valu

    attached the workbook fast , its my bed time

  16. #16
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: HELP PLEASE : How to make macros to search a word in the entire sheet & c/p the valu

    F25 is the same as G25, though, I told my code to look at columns B and G ONLY, for some unknown reason to me, it picks up the value of column G, so I had to change the name of F25 so as not to make it the same as G25. Apart from this glitch, the code works.
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    08-20-2013
    Location
    HK
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: HELP PLEASE : How to make macros to search a word in the entire sheet & c/p the valu

    Actually, i made few errors for column F25. Normally, Col F and G must not be the same. For instance in Column F25, it should be state as OR-IN while Column G25 is Total OR-IN.

    Can you tell me how did you do? Can you paste the code in here?
    Are u using vba code, right?

  18. #18
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: HELP PLEASE : How to make macros to search a word in the entire sheet & c/p the valu

    The code is attached to the sheet.
    Go to developer-Visual basics, then you should see VBA project-That is where the code is stored9(In the module). If you are happy with the outcome, I will make you a button. The button is attached to the code. When you press the button the code is run.

  19. #19
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: HELP PLEASE : How to make macros to search a word in the entire sheet & c/p the valu

    The code is now attached to a button. See the button on column O
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    08-20-2013
    Location
    HK
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: HELP PLEASE : How to make macros to search a word in the entire sheet & c/p the valu

    ok will check..

    By the way, if im going to apply this code to my other worksheet, how should i paste it? please correct me if im wrong.
    OPen my excel sheet > Developer > visula basic > double click the name of the worksheet > then paste the code?
    or should put it in module?

    sorry about my question. im still newbie with vba and im trying to learn and love this one

  21. #21
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: HELP PLEASE : How to make macros to search a word in the entire sheet & c/p the valu

    The code should go in to standard module, not in a worksheet.
    Once you are in the module, select the entire code, copy and then go in to the sheet you want to copy in to, insert new module, right click then paste.
    After the code is in standard module, you need to press F5 to run it, but you also need to change the name of the sheet from sheet1 to the actual sheet name, otherwise, you will get an error "Out of range"

  22. #22
    Registered User
    Join Date
    08-20-2013
    Location
    HK
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: HELP PLEASE : How to make macros to search a word in the entire sheet & c/p the valu

    Hi AB33,

    Thank you! It is really very helpful. I tried to changed the location of the word to be searched. Instead in column K, i transfer it at Column J. Once I press F5, I encountered error.
    I tried to change "K1" to "J1" but when i press F5, instead the values should appear in Col L and Col M, it appears the data in Col K and Col L. Did I miss a spot in here?

    Also, I wanted to also, if there were new datas will be encoded for Column C, Col D and Col H and Col I, the data in Col L and Col M will be changing, right? In that case, how can I refresh it? Is there any button or should I copy and paste the code again and press F5 to run again?

  23. #23
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: HELP PLEASE : How to make macros to search a word in the entire sheet & c/p the valu

    Should be

    Please Login or Register  to view this content.
    Before you run the code this line clears the existing data

    Please Login or Register  to view this content.

  24. #24
    Registered User
    Join Date
    08-20-2013
    Location
    HK
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: HELP PLEASE : How to make macros to search a word in the entire sheet & c/p the valu

    thank you for your help really appreciate it.
    by the way,one more thing, does this macro can be working on open office too?
    i tried but i think i encountered some errors nor macros are not working at all..

  25. #25
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: HELP PLEASE : How to make macros to search a word in the entire sheet & c/p the valu

    Honestly, I do not know! I have never tried a code in open office.

  26. #26
    Registered User
    Join Date
    08-20-2013
    Location
    HK
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: HELP PLEASE : How to make macros to search a word in the entire sheet & c/p the valu

    okay no problem.. thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Macro to search sheet 1 for value and insert entire row from sheet 2
    By mtlong88 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-09-2011, 01:21 PM
  2. Replies: 1
    Last Post: 11-11-2009, 05:54 PM
  3. How to search for a word in a column and have entire row opaque once word is found
    By copleyr in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 09-04-2009, 07:11 PM
  4. search function but how do i make it select the entire row
    By BouncingHippo in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-14-2008, 11:20 PM
  5. [SOLVED] How to Search for word in the Entire Workbook
    By lbbss in forum Excel General
    Replies: 4
    Last Post: 07-04-2005, 04:05 PM

Tags for this Thread

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