PDA

View Full Version : Use FIND, skipping one



brunces
08-03-2007, 08:37 AM
Title correction: Use FIND, skipping one cell.

Hey, friends.

Please, could anybody help me?

I've got this code below which checks if a value already exists in the sheet. If so, a MsgBox appears, asking me what to do (edit or delete the value - entire row). So far, so good. The problem is, the search value is in a cell (A4), then when I run the search, if the value doesn't exist, it finds cell A4, where the search value is. That's right, I know. But this is my problem... I don't want it to find A4. Here's the code.



If Not Cells.Find(What:=Range("A4").Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) Is Nothing Then
If MsgBox("Value already exist. Do you want to edit it?", vbYesNo) = vbYes Then
Application.ScreenUpdating = False
Cells.Find(What:=Range("A4").Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
Range("A4:B4").ClearContents
Application.ScreenUpdating = True
Else:
Range("A4").Select
Exit Sub
End If
Else:
(etc...)

I have to skip A4 value. I've tried to use these...



Range("A9", Selection.End(xlDown)).Find(What:=Range("A4").Value, ...

But it didn't work.

Could anyone give a little help? :)

Thanks for your attention.

Brunces

Bob Phillips
08-03-2007, 08:42 AM
Just check if the found cell address is $A$4, if so take appropriate action.

brunces
08-03-2007, 10:07 AM
I'm sorry, xld, I understand what you said, but I couldn't figure how to do this yet. Could you show me an example, please? Thanks for answering. :)

brunces
08-04-2007, 04:50 AM
Anyone, please? :)