Range.find

[claudia]

Hello,

I am not an experienced programmer but I am trying to write a VBA Excel macro to find a certain value in a range of cells and copy it to another cell in a different worksheet.
I found the following problem: Once you enter a value, if there is no such value in the range of cells, there is an error message from the compiler and the macro run is interrupted. The find command line I am using is:

Range("A10:A34").Find(What:=VlrNominal).Activate
t = ActiveCell.Value

where VlrNominal receives an input value from the user and variable t stores it.

Is there another way to look for a cell value avoiding this problem?
Please check if the above information is enough.
Thank you for your help.
Claudia

[Bob Phillips]

[vba]

On Error Resume Next
Set cell = Range("A10:A34").Find(What:=VlrNominal)
On Error Goto 0
If Not cell Is Nothing Then

t = cell.Value
End If
[/vba]

[mdmackillop]

[VBA]Dim t As Range
Set t = Range("A10:A34").Find(What:=vlrNominal)
If Not t Is Nothing Then
t.Copy Sheets(2).Range("A1")
End If[/VBA]

[xluser2007]

[vba]

On Error Resume Next
Set cell = Range("A10:A34").Find(What:=VlrNominal)
On Error Goto 0
If Not cell Is Nothing Then

t = cell.Value
End If
[/vba]

Bob,

Just to learn from this thread, I was testing this out as follows:

[vba]Option Explicit
Sub test()

Dim cell As Range
Dim t

On Error Resume Next
Set cell = Range("A1:A10").Find(What:="peace")
On Error GoTo 0
If Not cell Is Nothing Then

t = cell.Address
Debug.Print t

End If

End Sub[/vba]
As such could you explain the On error aprts.

That the On Error Resume Next tells it move to the next line of nothing is found. The On Error GoTo 0 clears all errors (is this right?). Then you re-test whether

[vba]If Not cell Is Nothing Then[/vba]
Why are the 2 error check's Bob? And not Just use the If Not Cell is Nothing?

Also How would you get the macro to loop through and find all possible occurrences of peace and print them in the loop - the FIND seems to pick up only the first one?

regards,

[Bob Phillips]

On Error Resume tells the error handler to simply resume at the next statement if an error is encountered, ignoring that error. You use it in circumstances where you expect a potential error but you don't want to crash and burn on that error. But you don't want to leave on error resume on, it might mask other errors later on, so you use use On Error Goto 0 to reset the error handler.

I think iun this case you can just test the result for Nothing, but there is one circumstance that elues me for the moment where it can fail, so I have just learnt to be cautious and wrap the find with an error catch.

[Bob Phillips]

To catch alll instances, you use Find and then FindNext in a loop. Take a look at Find in VBA help, it has a good example, and post back if something is not fully clear.

[xluser2007]

To catch alll instances, you use Find and then FindNext in a loop. Take a look at Find in VBA help, it has a good example, and post back if something is not fully clear.

Thanks for this suggestion Bob.

I followed the help example.

And basically set values in B2:B12. The macro searches for all ranges in B1:B500 containing 2 and replaces it with 25.

The strange this is that when it replaces a 26 with 25. This makes sense as we are isolating any address that has 2 in the value, and replacing that whole address value with 25.

How can you just replace 2 with 25, not any cell merely containg 2 as a digit amongst other digits e.g. not replacing 326 with 25, just because it contains a 2.

Also cell B1 is permanently set to 2, why is the macro not finding or replacing, and for that matter setting the "firstaddress" variable to be B1 instead of B2?

Thanks for your helpful suggestions.

I've attached my testing workbook.

[xluser2007]

On Error Resume tells the error handler to simply resume at the next statement if an error is encountered, ignoring that error. You use it in circumstances where you expect a potential error but you don't want to crash and burn on that error. But you don't want to leave on error resume on, it might mask other errors later on, so you use use On Error Goto 0 to reset the error handler.

I think iun this case you can just test the result for Nothing, but there is one circumstance that elues me for the moment where it can fail, so I have just learnt to be cautious and wrap the find with an error catch.

Well Explained Bob, I now understand the purpose of the On Error GoTo 0 now, being used straight after the On Error Resume Next Statement.

thanks and regards.

[xluser2007]

How can you just replace 2 with 25, not any cell merely containg 2 as a digit amongst other digits e.g. not replacing 326 with 25, just because it contains a 2.

I tried to address this proble using:

[VBA]
Sub test_Findnxt()
Dim c As Range
Dim firstaddress As String
With Worksheets(1).Range("b1:b500")

Set c = .Find(2, LookIn:=xlValues, LookAt:=xlWhole)

If Not c Is Nothing Then

firstaddress = c.Address
Debug.Print firstaddress
Do
c.Value = 25
Set c = .FindNext(c)
Loop While (Not c Is Nothing) And (c.Address <> firstaddress)
End If

End With
End Sub
[/VBA]
It seemed to replace only cells with a single 2 value in them, except tossed up a run-time error 91 "Object variable or With Block variable not set" and highlighted the following line:

[vba] Loop While (Not c Is Nothing) And (c.Address <> firstaddress)[/vba]

What is happening here and how can this be corrected for.

[Bob Phillips]

The problem is caused because you change the found values.

VBA does not short-circuit, so when you have an AND clause, it will evaluate both conditions, even if the first is true. In your case, after you have processed them all the c is Nothing, so c.Address cannot be evaluated. If you had just acted upon c, not changed it, it would have worked.

I remember now that this is a flaw in the help here, it is not quite as good as I suggested at first.

In reality, you only need to test for Nothing if you do change the values, or to be back where you started if you don't.

[xluser2007]

The problem is caused because you change the found values.

VBA does not short-circuit, so when you have an AND clause, it will evaluate both conditions, even if the first is true. In your case, after you have processed them all the c is Nothing, so c.Address cannot be evaluated. If you had just acted upon c, not changed it, it would have worked.

I remember now that this is a flaw in the help here, it is not quite as good as I suggested at first.

In reality, you only need to test for Nothing if you do change the values, or to be back where you started if you don't.

Bob, thanks for your helpful comments. Ijust tried to re-reun the original test program I had posted and it seems to run wothout the error, which is quite confusing.

Just to make sure that the program is robust, I woud like to implement your suggestions but am slightly unsure of your modifications suggested. Do you mean that we should change the last line from:

[vba]
Loop While (Not c Is Nothing) And (c.Address <> firstaddress)
[/vba]

to:

[vba]
Loop While (Not c Is Nothing)
[/vba]

Also, I'm quite unsure of why the first address variable is being picked up as $B$2, instead of $B$1, even if you set the $B$1 value to be 2. Could you please explain why this is so?

regards

[Bob Phillips]

Bob, thanks for your helpful comments. Ijust tried to re-reun the original test program I had posted and it seems to run wothout the error, which is quite confusing.

How odd - I am not seeing that.

Just to make sure that the program is robust, I woud like to implement your suggestions but am slightly unsure of your modifications suggested. Do you mean that we should change the last line from:

[vba]
Loop While (Not c Is Nothing) And (c.Address <> firstaddress)
[/vba]

to:

[vba]
Loop While (Not c Is Nothing)
[/vba]

Yep, that is what I mean.

Also, I'm quite unsure of why the first address variable is being picked up as $B$2, instead of $B$1, even if you set the $B$1 value to be 2. Could you please explain why this is so?

If you don't specify the After argument value, it will start at the top left hand cell of the specififed range, and find the first instance after this cell. Thus, if your rane is B1:B500, it will look for the firts value of 2 AFTER cell B1, which is B2 in your case, and B1 will be found last of all.

[claudia]

Hello,

I'd like to thank you all for your help. You saved me a lot of time.

Best wishes,
Claudia