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

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

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

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