Answered a little too soon!

Here's my attempt to integrate the answer into my code:

For Each ws In ActiveWorkbook.Worksheets
With ws
If Not (UCase(.Name) = "MASTER" Or UCase(.Name) _
= "COVER" Or UCase (.Name) = "CONTENTS" Or _
UCase(.Name) = "GENERAL SUMMARY") Then
.Unprotect
.Select

TargetColumn = Cells.Find(What:="ClientCost", _
After:=.Range("A1"), LookIn:=xlFormulas, lookat:=xlPart, _
searchorder:=xlByColumns, searchdirection:=xlNext, _
MatchCase:=False).Column

Set MyRange = Application.Intersect(.UsedRange, _
.Columns(TargetColumn))
On Error Resume Next
MyRange.SpecialCells(xlCellTypeConstants,
xlNumbers).ClearContents
MyRange.SpecialCells(xlCellTypeFormulas).ClearCont ents
On Error GoTo 0
End If
End With
Next

Why am I getting an Application-defined or object-defined error on the
Set line please?

Regards.

"Chip Pearson" wrote in message
...
Stuart,

Try something like the following:

Dim MyRange As Range
Dim TargetColumn As Integer
TargetColumn = 7
With ActiveSheet
Set MyRange = Application.Intersect(.UsedRange,
.Columns(TargetColumn))
End With


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"Stuart" wrote in message
...
If TargetColumn holds the value '7' and myRange is dimmed as
Range, how may I 'Set' myRange to get all populated cells in
Col 7,
so that I can then do:

On Error Resume Next
myRange.SpecialCells(xlCellTypeConstants, xlNumbers) _
.ClearContents
myRange.SpecialCells(xlCellTypeFormulas).ClearCont ents
On Error GoTo 0

Regards.


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.544 / Virus Database: 338 - Release Date:
25/11/2003






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.544 / Virus Database: 338 - Release Date: 25/11/2003