Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
A way to simplify this please
Can someone suggest a way to Simplify the following code.
If Cells(6, ccx) = Cells(3, 19) Or Cells(6, ccx) = Cells(3, 20) Or Cells(6, ccx) = Cells(3, 21) Or Cells(6, ccx) = Cells(3, 22) Or Cells(6, ccx) = Cells(3, 23) Or Cells(6, ccx) = Cells(3, 24) Or Cells(6, ccx) = Cells(3, 25) Or Cells(6, ccx) = Cells(3, 26) Or Cells(6, ccx) = Cells(3, 27) Then Cells(6, ccx).ClearContents End If Thank you Larry |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
A way to simplify this please
I assumed the range you are matching to is on Sheet3, so change as needed.
VBA can use some of excels worksheet functions, so I used the MATCH function. If a numeric value is returned (versus the #N/A error) clear the contents of the specified cell. Sub macro2() Dim rngMatch As Range With Worksheets("Sheet3") Set rngMatch = .Range(.Cells(3, 19), _ .Cells(3, 27)) End With With Application If IsNumeric(.Match(Cells(6, ccx), rngMatch, 0)) Then _ Cells(6, ccx).ClearContents End With End Sub "Larry Empey" wrote: Can someone suggest a way to Simplify the following code. If Cells(6, ccx) = Cells(3, 19) Or Cells(6, ccx) = Cells(3, 20) Or Cells(6, ccx) = Cells(3, 21) Or Cells(6, ccx) = Cells(3, 22) Or Cells(6, ccx) = Cells(3, 23) Or Cells(6, ccx) = Cells(3, 24) Or Cells(6, ccx) = Cells(3, 25) Or Cells(6, ccx) = Cells(3, 26) Or Cells(6, ccx) = Cells(3, 27) Then Cells(6, ccx).ClearContents End If Thank you Larry |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
A way to simplify this please
From your initial code I see the range you are matching to is on the same
worksheet and that worksheet must be the active sheet so With Worksheets("Sheet3") Set rngMatch = .Range(.Cells(3, 19), _ .Cells(3, 27)) End With could be changed to Set rngMatch = Range(Cells(3, 19), Cells(3, 27)) "JMB" wrote: I assumed the range you are matching to is on Sheet3, so change as needed. VBA can use some of excels worksheet functions, so I used the MATCH function. If a numeric value is returned (versus the #N/A error) clear the contents of the specified cell. Sub macro2() Dim rngMatch As Range With Worksheets("Sheet3") Set rngMatch = .Range(.Cells(3, 19), _ .Cells(3, 27)) End With With Application If IsNumeric(.Match(Cells(6, ccx), rngMatch, 0)) Then _ Cells(6, ccx).ClearContents End With End Sub "Larry Empey" wrote: Can someone suggest a way to Simplify the following code. If Cells(6, ccx) = Cells(3, 19) Or Cells(6, ccx) = Cells(3, 20) Or Cells(6, ccx) = Cells(3, 21) Or Cells(6, ccx) = Cells(3, 22) Or Cells(6, ccx) = Cells(3, 23) Or Cells(6, ccx) = Cells(3, 24) Or Cells(6, ccx) = Cells(3, 25) Or Cells(6, ccx) = Cells(3, 26) Or Cells(6, ccx) = Cells(3, 27) Then Cells(6, ccx).ClearContents End If Thank you Larry |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
A way to simplify this please
JMB
Thank you it works very well Larry On Wed, 28 Jun 2006 18:04:02 -0700, JMB wrote: From your initial code I see the range you are matching to is on the same worksheet and that worksheet must be the active sheet so With Worksheets("Sheet3") Set rngMatch = .Range(.Cells(3, 19), _ .Cells(3, 27)) End With could be changed to Set rngMatch = Range(Cells(3, 19), Cells(3, 27)) "JMB" wrote: I assumed the range you are matching to is on Sheet3, so change as needed. VBA can use some of excels worksheet functions, so I used the MATCH function. If a numeric value is returned (versus the #N/A error) clear the contents of the specified cell. Sub macro2() Dim rngMatch As Range With Worksheets("Sheet3") Set rngMatch = .Range(.Cells(3, 19), _ .Cells(3, 27)) End With With Application If IsNumeric(.Match(Cells(6, ccx), rngMatch, 0)) Then _ Cells(6, ccx).ClearContents End With End Sub "Larry Empey" wrote: Can someone suggest a way to Simplify the following code. If Cells(6, ccx) = Cells(3, 19) Or Cells(6, ccx) = Cells(3, 20) Or Cells(6, ccx) = Cells(3, 21) Or Cells(6, ccx) = Cells(3, 22) Or Cells(6, ccx) = Cells(3, 23) Or Cells(6, ccx) = Cells(3, 24) Or Cells(6, ccx) = Cells(3, 25) Or Cells(6, ccx) = Cells(3, 26) Or Cells(6, ccx) = Cells(3, 27) Then Cells(6, ccx).ClearContents End If Thank you Larry |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need to simplify this... | Excel Worksheet Functions | |||
please help simplify | Excel Programming | |||
please help simplify | Excel Programming | |||
please help simplify | Excel Programming | |||
Simplify | Excel Programming |