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
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