VBA Find

  • Hi There


    I guess I am just having an off day.


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

    Code
    =OFFSET(Validate!$R$1,0,0,COUNTA(Validate!$R:$R),1)


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


    Code
    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
                r.Select
                Exit Sub
            Else
                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.


    Thanks

  • 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

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


    Ok, seemed a simple fix,


    I have added the code

    Code
    .Activate


    after With r


    and then i did with

    Code
    activecell.offset(0,1)


    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.

    Code
    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)
                .ClearContents 
                .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!