On Error Message

[Peverley22]

I am a new member and a complete novice where VBA is concerned. I have tried to resolve this issue but am now struggling. When I run my code if it fails to find what it is looking for I get an error message. I want it to return a message directing the user to enter "Comp" in the cell correcly. Can anyone assist here?

Please Login or Register  to view this content.

[sktneer]

May be this......

Please Login or Register  to view this content.

[ragulduy]

Please Login or Register  to view this content.

[Peverley22]

Hi Yudlugar
Many thanks for your resolution to my problem but I am a novice here and have no idea where to place your code in mine. I am sorry to be a pain

Peter

[ragulduy]

Peter, replace your code with mine.

[ragulduy]

Also, you could make a loop to do this for all rows with comp in, that might be better?

Please Login or Register  to view this content.

[Peverley22]

Hi Yudlugar

The code below seems to be cutting and pasting both the row in which "Comp" is found and the cell the active cell from where the macro was started. It needs to just cut and paste the row on which "Comp" is found and shift all the rows below up.

Really sorry to continue to be a pain but your help is very much appreciated

Peter

[Sub CompVoid()
Dim rng
Set rng = Cells.Find(what:="Comp", After:=ActiveCell, LookIn:=xlFormulas, lookat _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
True, SearchFormat:=False).Activate
If rng Is Nothing Then
MsgBox "Enter Comp in cell correctly"
Exit Sub
Else
Do Until rng Is Nothing
Rows(rng.Row).Cut Sheets("CompVoids").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Sheets("LiveVoids").Select
Selection.Delete shift:=xlUp
Set rng = Cells.FindNext(rng)
Loop
End If
End Sub]

[Peverley22]

Hi Yudlugar

This code is great although there is one snag. When the search finds "Comp" it does not activate that cell and subsequently the delete command deletes both row and the cell that is currently active (basically the active cell where the code was ran from)

Peter

[ragulduy]

Sorry, I don't understand. Please can you upload an example workbook, click go advanced and then manage attachments. If you could include a brief description of what the macro is supposed to do that would be helpful.

[Peverley22]

Yudlugar

I have attached the workbook as requested.

The idea here is that the LiveVoids sheet contains the list of all properties where repair works are ongoing. Once complete each property can be identified by entering "Comp" in the first column in the appropriate row. I then want the macro to pick up any row identified as complete ("Comp")and move it to the CompVoids sheet at the next available blank row.

I hope this helps

And once again many thanks for your valuable assistance

Peter