Help with a macro producing incorrect results if data in rngC - please

[colink2]

Hi

In this macro I have
rngA (Column A excluding Title)
rngC (Column C excluding Title)
rngOtherColumns (All columns to the right of C)

What should happen when run
Delete all values in rngC that = "Used", keep all other values
Loop through rngA
Where an exact match is found in rngOtherColumns on the same write "Used" rngC on the same row as the value searched for.

2 error scenarios
If rngC contains no values, the macro correctly writes "Used" to the correct cell - BUT, it deletes "used" Title
If rngC contains values anywhere in the column it writes "Used" to the cell below where it should be written - it does not delete the "used" title

Clearly some offset is occurring if data exists in Column C, I assume the same offset is protecting the title.

Any help would be appreciated. ColinK

[BadlySpelledBuoy]

Add the parts in red from the snippet of your code below:

    If Not foundCell Is Nothing Then
        If rngC.Cells(cell.Row - 1, 1).value = "" Then
            rngC.Cells(cell.Row - 1, 1).value = "Used"
        End If
    End If

For info, it did not delete the "used" title for me.

BSB

[colink2]

Thanks for your prompt reply and suggested solution

That is better but some anomalies
If I start with column C empty, including Title, it misses Cow - and puts Used in the budgie row
If I put any random text anywhere in Column C (leaving title blank) t works OK
If I have the Title Used but no other data in column C it deleted the title and returns the correct results
If I have the Title Used AND random data anywhere in column C it deleted the title and returns correct results for each row except Horse (row 2) - see screenshot

I can probably live with that, however the title or no title in Column C should have no affect on the macro.Attachment 814453

[beyond Excel]

Hello colink2.
Try with:

Sub ColC_Used_New()
Dim a, Q&, i&, Rng As Range, C As Range
a = Cells(1).CurrentRegion: Q = UBound(a)
If Range("D1") = "" Then Range("D1") = "."
Set Rng = Range("D1", [a1].SpecialCells(xlLastCell))
For i = 1 To Q
  Set C = Rng.Find(a(i, 1), LookIn:=xlValues, LookAt:=xlWhole)
  If C Is Nothing Then a(i, 1) = Empty Else a(i, 1) = "Used: " & C.Address(0, 0)
Next
Columns("C").ClearContents: Range("C1").Resize(Q) = a
End Sub