Check relative / absolute references

[oli]

Hello,

I want to find the cells with absolute reference and the ones without abolute reference.

I tried this way (seems to work ...):


Option Explicit

Sub check_ref()
Dim vAnzZeilen As Long, vZnr As Long
Dim vInCell As String
vAnzZeilen = Worksheets(1).UsedRange.Rows.Count
MsgBox "rows: " & vAnzZeilen

For vZnr = 2 To vAnzZeilen
vInCell = Worksheets(1).Cells(vZnr, 1).FormulaLocal
' check content of the cell
If InStr(vInCell, "$") Then
Debug.Print vInCell & ": absolute"
Else
Debug.Print vInCell & ": relative"
End If
Next vZnr
End Sub

Is there a better way (function or method) to do this.

I would like to do the same check to find which cells refenrences a name.

Thanks.

Olivier

[Tom Ogilvy]

In my opinion:
for checking column A, that would pretty much be it. You could narrow it
down to only look at cells with formulas by using specialcells, but for
absolute and relative, checking the formula string would be required. As
for searching for a name, seeing if the formula string contained the name
would be as good as any.

--
Regards,
Tom Ogilvy

"oli" <[email protected]> wrote in message
news:[email protected]...
>
> Hello,
>
> I want to find the cells with absolute reference and the ones without
> abolute reference.
>
> I tried this way (seems to work ...):
>
>
> Option Explicit
>
> Sub check_ref()
> Dim vAnzZeilen As Long, vZnr As Long
> Dim vInCell As String
> vAnzZeilen = Worksheets(1).UsedRange.Rows.Count
> MsgBox "rows: " & vAnzZeilen
>
> For vZnr = 2 To vAnzZeilen
> vInCell = Worksheets(1).Cells(vZnr, 1).FormulaLocal
> ' check content of the cell
> If InStr(vInCell, "$") Then
> Debug.Print vInCell & ": absolute"
> Else
> Debug.Print vInCell & ": relative"
> End If
> Next vZnr
> End Sub
>
> Is there a better way (function or method) to do this.
>
> I would like to do the same check to find which cells refenrences a
> name.
>
> Thanks.
>
> Olivier
>
>
> --
> oli
> ------------------------------------------------------------------------
> oli's Profile:
http://www.excelforum.com/member.php...o&userid=24000
> View this thread: http://www.excelforum.com/showthread...hreadid=384474
>