VBA Find

  • Hi There

    I guess I am just having an off day.

    I have a dynamic Range in Excel ("ExamFees")


    However, i now want to have code that tries to find the value of the combobox against the dynamic Range

    Dim r As Excel.Range
        Set r = Sheets("Validate").Range("ExamFees").Find(what:=ComboBox1.Value, lookat:=xlWhole)
            If Not r Is Nothing Then '//Was found
                Exit Sub
                MsgBox "Not found"
                Exit Sub
            End If

    However even if the I know the combobox matches something of the dynamic Named range, it still only get the msgbox, and does not select "r".

    I have thought about a do while, but not sure how I would write that.

    No doubt a genius on here will solve my problem.


  • Re: VBA Find

    Ok, i have change the way I am doing this, but sill it seems to Fail.

    What I simply want to happen is if a cell with the range is the same as the combobox1.value then delete that cell and the one to the right

    The code now is:

    I can see that r gets selected, but for some reason, it will not loop through the cells with in r and check against the combobox.

    Again any help would be appreciated.

  • Re: VBA Find

    The Combox1.value is from source "LongTerm"

    and i have done a simple excel formula to check that the individual item in "LongTerm", matches the individual item in "ExamFees", and they do.

    I do not know why when I select an item from ComboBox1 my code is then unable to locate that same value in "ExamFees"

    I will upload the file.

    There are lots of other code etc, but the problem is in

    Form FMCourse, and within Sub CommandButton1_Click()

    I have marked the error with "'Delete Exam too (If Exists) ///DOes not work yet\"

    Hope this helps

  • Re: VBA Find

    Thankyou, I have copied a required small sample of my data into yours and it works. Just one thing:

    I am not wanting to delete the entire row, just the combobox1.value and the cell to the right.

    I have used:

    but i get an object required error on line

    With r.Offset(0, 1)
  • Re: VBA Find

    Ok, seemed a simple fix,

    I have added the code


    after With r

    and then i did with


    It appears it was not actually activating the cell.

  • Re: VBA Find

    Thank you all for your help.

    I apologise if i seemed a bit ignorant.

    I have see that the Combobox.value is cleared once I delete it from the rowsource.

    I have gotten around this by placing the value of the combobox in a cell, then referencing that cell to clear from "ExamFees"

  • Re: VBA Find

    Try this.I'm not sure if you want to clear the cell or delete it - you don't need to do both here.

    With Range("ExamFees") 'Was "Names"
       Set r = .Find(What:=Me.ComboBox1.Value, Lookat:=xlWhole, MatchCase:=False, SearchFormat:=False)
        If Not r Is Nothing Then 
            With r.resize(,2)
                .Delete shift:=xlUp 
             End With 
         End If 
    End With

Participate now!

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