Results 1 to 5 of 5

Thread: VBA for EXCEL: Find specific text in a column of cells with text.

  1. #1

    Thread Starter
    Registered User
    Join Date
    Mar 2014
    Posts
    1

    Talking VBA for EXCEL: Find specific text in a column of cells with text.

    Hi guys,

    Hoping you guys could give me some guidance to solve this issue i had right now.

    Basically right now, I have 2 worksheet inside a single workbook

    Worksheet 1: Price
    Name:  PRICE.jpg
Views: 33430
Size:  42.7 KB

    Worksheet 2: Listing
    Name:  lisitng.PNG
Views: 32761
Size:  21.4 KB

    The above is just a sample. For your information, i got around 10000 of data's that i need to input the price value and its really troublesome if i use the old fashion way which is find and copy the price value. Therefore, i tried to use vlookup and some other excel formula, but it doesnt work at all.
    As far as i know vba come in really handy to solve this kind of issue and i am really new in this, hoping someone can guide me thru.

    Basically, I would like to use the text in cell of column A of Worksheet 2: Listing (For example: PAR-11257) and perform search in Worksheet 1: Price.
    After searching is done, the program will automatically take the price value ($$8787) of the same row and input it into the price column at Worksheet 2.

    Thanks for the help!

    Regards,

    Roy

  2. #2
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: VBA for EXCEL: Find specific text in a column of cells with text.

    Why is vLookup not working?

    If all the items on worksheet 2 are on worksheet 1 as well, and you have them both sorted by item, it should work.

    For example, I have a worksheet called "source" that has the data, and am looking to populate the price on my other worksheet, and this works:

    Code:
    =VLOOKUP(A2,source!A2:C11,2)
    cell A2 has the value I'm looking up, and the data in "source" goes from A2 to C11, with the price being in column 2 (or B)

  3. #3
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: VBA for EXCEL: Find specific text in a column of cells with text.

    If I understand correctly, you want a positive match if the lookup table value contains the text in the source.

    As you indicate you are new to VBA let us do this by the numbers.

    1. Open the VBA Editor (Alt-F11 from Excel)
    2. Insert a Module (Alt-IM - hold Alt key down, type I followed by M)
    3. Copy the code below to the module.
    4. Go back to Excel and use the new function.

    VBA Code:
    1. Public Function VContains(what As Range, SourceRange As Range, ReturnColumn As Integer) As Variant
    2.       Dim strWhat As String
    3.       strWhat = CStr(what.Value)
    4.      
    5.       Dim row As Integer
    6.       row = 0
    7.      
    8.       Dim found As Boolean
    9.      
    10.       Do While (Not found) And (row <= SourceRange.Columns.Item(1).Cells.Count)
    11.          row = row + 1
    12.          found = CStr(SourceRange.Columns.Item(1).Cells(row).Value) Like "*" & strWhat & "*"
    13.       Loop
    14.      
    15.       If found Then
    16.          VContains = SourceRange.Cells(row, ReturnColumn).Value
    17.       Else
    18.          VContains = CVErr(xlErrNA)
    19.       End If
    20. End Function

  4. #4
    Addicted Member 3com's Avatar
    Join Date
    Jul 2013
    Location
    Spain
    Posts
    253

    Re: VBA for EXCEL: Find specific text in a column of cells with text.

    If you prefer to go via formula then do the following:

    You have to create a dynamic range name in column A and column B.
    The first called "description", and the second called "prices".

    Name:  Description.png
Views: 32450
Size:  14.4 KB

    After that, you go to the worksheet called <prices>, and enter this formula in the first cell in column B.
    =LOOKUP(A2;Description;Prices)

    Then fill the other cells with this formula. Refor to the pic below...

    Name:  Prices.png
Views: 32614
Size:  27.2 KB

    HTH

  5. #5
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: VBA for EXCEL: Find specific text in a column of cells with text.

    Welcome to the forums roybong

    Since you are using Partial Matches, Simply using Vlookup/Lookup will not give you correct results. You need to take help of Index/Match and then use VLookup. Here is an example.

    Let's say your data looks like this

    Name:  1.png
Views: 32222
Size:  7.3 KB

    To find the text which has the partial match, you need to use Index/Match with wildcard characters. For example, Put this formula in cell E1

    =INDEX(A:A,MATCH("*"&D1&"*",A:A,0))

    This will return the text which contains the partial match.

    Name:  1.png
Views: 32414
Size:  9.4 KB

    We can now use this formula in the Vlookup to achieve what we want

    =VLOOKUP(INDEX(A:A,MATCH("*"&D1&"*",A:A,0)),A:B,2,0)

    Name:  1.jpg
Views: 32286
Size:  15.7 KB

    HTH
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

Posting Permissions

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



Click Here to Expand Forum to Full Width