can anybody explain this code?

This might be of some help.

Line 4 - Because of the With statement in line 2 this line is the equivalent of saying Let c be the first cell in Worksheets(1).Range("B1:B10") to contain the word 'in'. If for example the cell B4 contained the word in the c would be an object variable containing a reference to B4.

Line 5 - This means if C is something (Not Nothing=Something) then carry out line 6 else carry out line 8.

Line 6 - If the test in line 5 is true (that is that the macro found the word 'in' somewhere) then select that cell.

Line 11 - This gets rid of the memory used by the variable c. When a variable is defined memory is set aside and this statement simply releases it back to the operating system. In this case it won't really make any difference. However, it's good practice especially if you're working with lots of object variables.

Line 12 - This is the 'closing' part of line 2. You could actually change the code and get rid of the part with With... and End With to this:-

Sub find()
1 Dim c
2 '
3 On Error Resume Next
4 Set c = Worksheets(1).Range("B1:B10").find("in")
5 If Not c Is Nothing Then
6 Range(c.Address).Select
7 Else
8 MsgBox "in not found!"
9 End If
10 On Error GoTo 0
11 Set c = Nothing
12 '
End Sub

Finally, the line On Error Resume Next tells VBA to ignore any errors and jump to the next line that it's able to execute or to exit the procedure if it can't carry on. The line On Error Goto 0 resets this so that any errors will be stopped by VBA i.e. you'll get a error dialog box coming up telling you that something has gone wrong.

I hope this helps,
Daniel.

Thankyou

Thanks Daniel,

I am constantly amazed by this site and the generosity of people in giving time to answer questions like mine.

Cheers Andy

but...... can I trouble you further?

Dear Dan,

have read it through and now I get it........ all except the bit about 'on error.'

What error is likely to occur as part of the code that would need the line

on error resume next?

also I don't quite get the on error go to 0 explanation.

I'm really sorry to sound thick but students have a habit of homing in the bit that you can't clearly explain.

Andy

Re: but...... can I trouble you further?

In the case of this code it's unlikely that any errors will occur. However, try this.

Put this code into any workbook.

Sub find()
Dim c
On Error Resume Next
With Worksheets(1).Range("B1:B10")
Set c = .find("in")
If Not c Is Nothing Then
Range(c.Address).Select
Else
MsgBox "in not found!"
End If
On Error GoTo 0
Set c = Nothing
End With
End Sub

Notice that I've moved the On Error Resume Next to the start of the procedure so that it will catch all errors.

Now, in your workbook insert a blank chart and delete any worksheets in your workbook.

Run the macro. Nothing will happen. Now remove the On Error Resume Next line and run the macro again. You'll get an error message because the macro is trying to work with the first worksheet (Worksheets(1).Range("B1:B10")) but there is no worksheet 1.

The error code is only there as a fail safe and it's normally a good idea to include precautions (Horrible error messages don't look good for customers or students!)

I hope that part is clear. As for the On Error Goto 0 it just resets error handling. Consider this code:-

Sub CauseAnError()
Dim intResult As Integer

On Error GoTo 2 'Any errors will cause VBA to jump to line 3

1 intResult = 50 / 0 'Cause a division by zero error

4 On Error GoTo 0 'Reset error handling
5 intResult = 50 / 0 'Try and cause another error.
6 Exit Sub
2 MsgBox "An error occurred and was trapped by this procedure."
3 Resume Next 'Will jump to the next line in code after the error occurred.
End Sub

The numbers show the order in which the lines of code are executed. With error trapping enabled (On error goto 2) the error is handled by the procedure. When error handling is turned off (On Error Goto 0) an the procedure tries to divide by zero again VBA takes over and you see an inbuilt message.

Regards,
Daniel.

Re: but...... can I trouble you further?

Daniel,

Thanks you are a star!

Wish I could repay you!

Andy