Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlight a Circular Ref
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlight a Circular Ref
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 he Next noFormulas: Exit Sub noPrecedent: Resume here End Sub Regards, Peter T "solomon_monkey" wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlight a Circular Ref
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... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlight a Circular Ref
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 "solomon_monkey" wrote in message oups.com... 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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Circular or semi-circular chart | Charts and Charting in Excel | |||
how to highlight more related cells if cell highlight | Excel Discussion (Misc queries) | |||
Circular | Excel Worksheet Functions | |||
Circular | Excel Worksheet Functions | |||
Highlight cells with ctrl-click but only un-highlight one cell | Excel Discussion (Misc queries) |