Find all Cells containing a string that matches the string in a designated cell

    First of all, I link cell A1 of Sheet 1 to a Combo Box whose list contains a series of strings. Now after I pick one of the strings from the Combo Box's list, I want to use VBA to find all the cells in Sheet 2 that contain strings matching the one I selected from the Combo Box list. How do I this?


    I am aware of the Cells.Find function:


    Code
    Cells.Find(What:=, After:=ActiveCell, LookIn:=xlValues, LookAt:= _        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , MatchByte:=False, SearchFormat:=False).Activate


    I tried to specify Cell A1 of Sheet 1 after the "What:=" line above but VBA keeps giving me error messages. Is there a workaround?

    Re: Find all Cells containing a string that matches the string in a designated cell


    You will need to declare a range - and run a for next loop through the range checking each cell to see if the text exists.


    It's reasonably straight forward - I'm assuming you want the code to run after the combobox after exit or change - so something like


    set myrange = range("A1", ("A1").end(xldown)) - (so this is looking for the last empty cell in column A1 but it could be range whatever)


    for each c in myrange - checks each cell in your defined range


    if instr(UCase(c.text), UCase("The cell your checking against")) > 0 then - looks for any matching text


    do whatever - unsure what your trying to do if a match found


    end if


    next


    Or you could do it using values through the loop - rather than using Instr


    so - if c.value = range(whatever your cell is).value then


    do whatever


    end if
    next

    Re: Find all Cells containing a string that matches the string in a designated cell


    Thanks philby! After some experimenting, I got it to work using your first method which involves For Each... Then.


    However VBA kept giving me error messages for the following code line:


    Code
    Set myrange = Worksheets(2).Range("B3", ("B3").End(xlToRight)


    I was attempting to use this code line to compare a region of string-containing cells in Sheet 2 (these cells are all in row B, starting from B3) against the string in my A1 cell in Sheet 1. How should I go about doing this?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login