Subtracting a cell from an entire range in excel VBA

[devenderkush1983]

Hi All,

I am trying to search for a particular value in a range but want to exclude a particular cell from this range as well as search. Following is my code

range:

Set FoundCell = ws.Range("A1:F" & lastRow).Find(What:=WHAT_TO_FIND)

cell that needs to be excluded from the range and search :

ws.Cells(found_row, found_par_col)

Thanks,
Dev

 

[JLGWhiz]

There is not a method that will exclude the cell from the search if ws.CellsIfound_row, found_par_col) falls within the A:F range. But What you can do is include the FindNext method and use and If...Then statement to by pass the cell if it is found.
Example:

Set FoundCell = ws.Range("A1:F" & LastRow).Find(What:=WHAT_TO_FIND)
    If Not FoundCell Is Nothing Then
        Do
            If FoundCell.Address = ws.Cells(found_row, found_par_col).Address Then
                Set FoundCell = ws.Range("A1:F" & LastRow).FindNext(FoundCell)
            End If
        Loop Until FoundCell.Address <> ws.Cells(found_row, found_par_col).Address
    End If