ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Amending code question (https://www.excelbanter.com/excel-programming/283917-amending-code-question.html)

Stuart[_5_]
Amending code question
 
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

Chip Pearson[_2_]
Amending code question
 
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

Stuart[_5_]
Amending code question
 
Many thanks.

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

Stuart[_5_]
Amending code question
 
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

Chip Pearson[_2_]
Amending code question
 
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

Stuart[_5_]
Amending code question
 
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


All times are GMT +1. The time now is 11:41 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com