Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Amending a formula | Excel Discussion (Misc queries) | |||
Amending another formula | Excel Discussion (Misc queries) | |||
Amending date formats. | Excel Discussion (Misc queries) | |||
Help amending code | Excel Discussion (Misc queries) | |||
Help with Amending this Code Please | Excel Worksheet Functions |