Reset / Clear the Union Object

[DominicB]

Hi all

I have a piece of code which is to loop through all the worksheets in a workbook and select only those cells containing a formula, for a selective find / replace to take place. I am using the Union object thus :

For Each UsrRng In Selection
If UsrRng.HasFormula = True Then
Found = Found + 1
If Found = 1 Then Set CellCollection = UsrRng
If Found <> 1 Then Set CellCollection = Union(CellCollection, UsrRng)
End If
Next UsrRng
CellCollection.Select

This works perfectly on the first worksheet, but falls down on the next sheet in line – the debugger pointing to this command as the problem:

= Union(CellCollection, UsrRng)

Is this because the Union still contains range information from the first worksheet? How can I clear it out?

Thanks

DominicB

 

[Norie]

Dominic

I think that might be the problem, you can't union across worksheets

Why not just use this before you move onto the next worksheet.

Set  CellCollection = Nothing

 

[DominicB]

Hi Norie

Thanks for the suggestion - I actually thought that would sort it out

But is hasn't

Any other thoughts or suggestions?

DominicB

 

[Norie]

Dominic

I honestly thought that would have done it too.

Didn't test it as it seemed so obvious.

Can you post the rest of your code?

 

[Norie]

Right, just tested.

Need to set Found to 0 as well.

 

[DominicB]

Hi Norie

Sure.

For Each WkBk In ActiveWorkbook.Worksheets
Set CellCollection = Nothing
WkBk.Select
Range("A1").Select
Set LastCell = Cells(Range(Cells(1, 1), ActiveSheet.UsedRange).Rows.Count, Range(Cells(1, 1), ActiveSheet.UsedRange).Columns.Count)
Set ag = Selection.Resize(LastCell.Row, LastCell.Column)
For Each UsrRng In ag
If UsrRng.HasFormula = True Then
Found = Found + 1
If Found = 1 Then Set CellCollection = UsrRng
If Found <> 1 Then Set CellCollection = Union(CellCollection, UsrRng)
End If
Next UsrRng
CellCollection.Select
Selection.Replace What:=Rnme1, Replacement:=Rnme3
Next WkBk

Basically, ag is setting the whole used range of a worksheet, from A1, to the last used row / column. UsrRng then goes through each cell and if it's a formula, it's added to the Union object, which now is cleared prior to each sheet being "done".

It works beautifully. On the first sheet

Thanks for taking the time to look.

DominicB

 

[DominicB]

Aaaaaaaaaargh!!!!!

How could I miss that one?!?!?

Sometimes I'm so stupid. And you're not!

Cheers Norie

DominicB

 

[Fazza]

What about using SpecialCells(xlCellTypeFormulas)? Something like

Dim wks As Worksheet

On Error Resume Next
For Each wks In ActiveWorkbook.Worksheets
    wks.Cells.SpecialCells(xlCellTypeFormulas).Replace What:=Rnme1, Replacement:=Rnme3
Next wks

Set wks = Nothing