How to assing a flag value based on the value of a Named Range?

[feipezi]

Suppose I have a Named Range called "data": $a$1:$e$10. What I want is if any of cells of "data" bears a value of 10, a character of "*" will be put in Column("f") in the same row. I used an Array Formula: {=IF(data="10","*","")} but it's not working so I switched to VBA. Here is the code:


Sub test(tgt As String, tgtcol As String)
Dim FCell As Range
Dim LCell As Range
With Range("data")
Set LCell = .Cells(.Cells.Count)
End With
Set FCell = Range("data").Find(what:=tgt, after:=LCell)
If Not FCell Is Nothing Then
FrstAddr = FCell.Address
End If
Do Until FCell Is Nothing
If FCell.Address <> "" Then Cells(FCell.Row, tgtcol) = "*"
Set FCell = Range("data").FindNext(after:=FCell)
If FCell.Address = FrstAddr Then Exit Do
Loop
End Sub

Sub gogetit()
test "10", "P"
End Sub


It works but if the Named Range is part of a Pivottable, then it will run very, very slow. Or, it's fine. Unfortunately, I need it for a PT.

Any suggestions?

Thanks in advance.

 

[SkipVought]

Hi,

Check forum68 for the spreadsheet solution.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}