Advice on formula Please

[Tempy]

Good Morning, i got the following code from Tom Ogilvy which works
really great & would like to know if one can use the same formula to
search for a number e.g. "0" or "1" ?

On Error Resume Next
set rng = columns(22).Specialcells(xlConstants,xlErrors)
On Error goto 0
If not rng is nothing then
Intersect(Range("V:AB"),rng.EntireRow).ClearContents
End If

Sorry for the ignorance, but i am fairly new to this game

Tempy

*** Sent via Developersdex http://www.developersdex.com ***

 

[Dave Peterson]

There's nothing built into .specialcells to look for an individual value (like 0
or 1).

But if you don't have any errors in that range, you could change the 0s (or 1s)
to #n/a's and then use your existing code.

You could always loop through the cells.

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range

With ActiveSheet
Set myRng = .Range(.Cells(1, 22), .Cells(.Rows.Count, 22).End(xlUp))
For Each myCell In myRng.Cells
If IsEmpty(myCell) Then
'skip it??
Else
If myCell.Value = 0 _
Or myCell.Value = 1 Then
Intersect(.Range("V:AB"), myCell.EntireRow).ClearContents
End If
End If
Next myCell
End With

End Sub

 

[Tempy]

Hi Dave,

I tried your code and it does not seem to work. What i did omit to say
is that the the 0 is a result od a Vlookup, findine the lookup value on
both sheets but the required field is empty. Could this be why it is not
working ? The entire column is then copied and pasted back as just
values to get rid of the formula's.

Tempy

*** Sent via Developersdex http://www.developersdex.com ***

 

[Tempy]

Help please !!!
This is really frustrating me, gonna go home and have a beer !!
Speak again in the morning.

Regards

Tempy

*** Sent via Developersdex http://www.developersdex.com ***

 

[Tom Ogilvy]

I would say your description of your problem in response to Dave's solution
is so incomprehensible and lacking detail that your chances of an answer are
very small.

The clearer your explanation of what you are trying to do, the more likely
you are to receive assistance. Based on your first posting in this thread,
Dave's solution is quite adequate. Your response

What i did omit to say
is that the the 0 is a result od a Vlookup, findine the lookup value on
both sheets but the required field is empty.

Who knows what the "required field" is or the significance of it being
empty. Vlookup works on one range. How does it result in a zero for
finding the "lookup" value on "both sheets". In any event, that should be
immaterial. Dave's code loops through column V and clears cells where a
cell in column V has a numeric value of zero or a 1. Again, it should be
immaterial how that value is produced, even if you have replaced the formula
in the cell with the value it displayed.

It is unclear why you say it doesn't work.