Highlight a Circular Ref

[solomon_monkey]

Very final post today-

I want to write a macro so that in column b of row 'x' of a spreadsheet
I will have a vlookup that will look at all the previous lines of the
spreadsheet.

I thought I could get around doing this by vlooking up all of the
column. However, this creates a circular reference that I cannot
find... so second question- is there any sort of 'if' statement that
would delete a circular reference and instead write text such as '#N/A'
or 'HERE'?

Many thanks.

Solomon

 

[Peter T]

Hi Soloman,

Chip Pearson suggested in this ng:
"
How about seeing if the Intersection of the cell and its precedents is
Nothing? If so, that cell does not have a circular reference. If not, it
does. E.g.,

If Not Application.Intersect(c, c.Precedents) Is Nothing Then
Msgbox "Has Circular Referece"
End If
"

which I've adapted

Sub test()
Dim rng As Range, cell As Range
On Error GoTo noFormulas
Set rng = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 23)
On Error GoTo noPrecedent
For Each cell In rng
If Not Application.Intersect(cell, cell.Precedents) Is Nothing Then
cell.Interior.ColorIndex = 3 'colour circ-ref red
'or delete & write some text
'cell = "HERE"
End If
here:
Next
noFormulas:
Exit Sub
noPrecedent:
Resume here
End Sub

Regards,
Peter T

 

[solomon_monkey]

Thanks Peter... however, being as the vlookup is in column B regarding
column A and looks in A & B every reference is a circular reference...
this is fine for what I want it to do to a point... but only matters
when a vlookup would otherwise return an #N/A... Sorry to be awkward...

 

[Peter T]

Without recreating your scenario I'm not quite sure what you need. If you
want to refine the search range to cells with errors, change

..SpecialCells(xlCellTypeFormulas, 23) 'all formulas
to
..SpecialCells(xlCellTypeFormulas, 16) 'formula errors only

Or record a macro, press F5 Special cells and whatever is appropriate.

And/or also some a nested If's perhaps

If InStr(cell.Formula, "VLOOKUP") then
if IsError(cell) then
If ... then

Regards,
Peter