Msgbox when nothing found through range loop

[Sc0tt1e]

I'm having a bit of a struggle trying to construct this code.

I realised I don't have any error trapping in the code and am looking to add a message box if the loop doesn't find the value entered into the userform label. The code is basically something along the following lines and am not sure how to put the message box in the code (maybe I'm just being bit thick but I can't get my head around the lop until end but no values found, some help would be great).

[CODE]
For each C in range

with sheet1
paste some values somewhere
end with

find bottom row in range

with sheet 2
paste some values somewhere on bottom row
end with

next c

userform1.hide

msgbox "all done"
[\CODE]

[TMS]

Three choices spring to mind. If it is a "key" field and matches data in a column, you could use Countif to check if the value is there (before you start the loop). Or, you could use a counter if the number of matches is of interest; display your message if it is zero. Or you could use a Boolean "flag" to indicate that a match has been found. Again, display your message if the flag is False.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[Sc0tt1e]

Genius idea on the count, I keep forgetting to use worsheet functions to solve problems within VBA. So I have added the following

[CODE]
With application.worksheetfucntion
count = .countif(range,value)
end with

If (count=0) then
msgbox "It isn't bloody here OK!!!!"

else

Previous code

[CODE]

[TMS]

@John: the Else y = y is redundant. You just won't be incrementing y.

@Sc0tt1e: You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[JOHN H. DAVIS]

@ TMS. Duh Me.