Found my error....had not Dimmed TargetColumn As Integer.
So even though Locals showed a value of "7" (the correct
column) because TargetColumn was Dimmed As String, it
could not be accepted as an acceptable 'value' for a column.
I think(?).
Thanks again for the help.
Regards.
"Chip Pearson" wrote in message
...
Stuart,
Are you sure that TargetColumn is getting set to a positive
number? If Find doesn't find the text, TargetColumn will get a
zero value, and that will cause a 1004 error on the Set
statement.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
"Stuart" wrote in message
...
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
---
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