VBA Find

Hi There

I guess I am just having an off day.

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

=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

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:

Set r = Sheets("Validate").Range("ExamFees") '.Find(what:=ComboBox1.Value, lookat:=xlWhole)
       r.Select
       
        For Each cell In r 'Selection
            Do
                If ActiveCell.Value <> ComboBox1.Value Then
                    ActiveCell.Offset(1, 0).Activate
                End If
            Loop Until ActiveCell.Value = ComboBox1.Value
               With ActiveCell
                    .ClearContents
                    .Delete shift:=xlUp
                    With ActiveCell.Offset(0, 1)
                        .ClearContents
                        .Delete shift:=xlUp
                    End With
               End With
         Next cell
       
       Exit Sub

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:

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
        .ClearContents
        .Delete shift:=xlUp
            With r.Offset(0, 1)
                .ClearContents
                .Delete shift:=xlUp
            End With
        End With
        
    End If
End With

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

.Activate

after With r

and then i did with

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"