При удалении строки, нарушается ссылка на нее

[transgd]

Всем доброе время суток!
Что сделать, чтобы при удалении строки, ссылка на ячейку расположенную в этой строке осталась без изменений (и без: #ССЫЛКА!)?

[vikttur]

Тема на соседней странице
http://www.programmersforum.ru/showthread.php?t=109414

[transgd]

Извините, но в указанной вами теме я не нашел ответа на свой вопрос.

[transgd]

Попробовал давать имена ячейкам - то же самое.
Может кто-то из специалистов все-таки найдет время ответить на мой вопрос?
В поиске по форуму ответ пока найти не удалось.

[аналитика]

=ДВССЫЛ("A1")
эта формула всегда будет ссылаться на ячейку A1 (даже после удаления 1-ой строки )

[IgorGO]

см.вложение

[transgd]

аналитика
Спасибо, работает!
А можно автоматизировать замену всех ссылок (их у меня уже больше тысячи) на ДВССЫЛ?

[transgd]

IgorGO
Спасибо за помощь, сейчас проверяю на своих данных!

[аналитика]

Сообщение от transgd

А можно автоматизировать замену всех ссылок (их у меня уже больше тысячи) на ДВССЫЛ?

обработки ссылок на целые строки (напр., "2:2") нет!
проверяй на копиях!

Option Explicit

Sub InDirect_()
   Dim r As Range, c As Range
   Dim sIn As String, sRanges() As String, sNotRanges() As String, k As Integer
   Dim L As Integer, i As Integer
   Dim sChar As String * 1, sOut As String
   Dim bInRange As Boolean
   Dim iBegin As Integer, iEnd As Integer

   On Error Resume Next
   Set r = Selection.SpecialCells(xlCellTypeFormulas, 23)
   If Err <> 0 Then MsgBox "Нет формул в выделенной области!", vbCritical: Exit Sub
   On Error GoTo 0

   For Each c In r

      iBegin = 0: iEnd = 0: k = 0: sOut = ""

      sIn = c.FormulaLocal
      L = Len(sIn)
      For i = 1 To L
         sChar = Mid(sIn, i, 1)

         If Not bInRange Then
            If sChar Like "[A-Z]" Then
               bInRange = True
               iBegin = i

               k = k + 1
               ReDim Preserve sNotRanges(1 To k)
               ReDim Preserve sRanges(1 To k)

               sNotRanges(k) = Mid(sIn, iEnd + 1, iBegin - iEnd - 1)
            End If

         Else
            If sChar Like "[+-/*)&^; ]" Then
               bInRange = False
               iEnd = i - 1


               sRanges(k) = Mid(sIn, iBegin, iEnd - iBegin + 1)
            End If
            If bInRange And (i = L) Then
               bInRange = False
               iEnd = i

               sRanges(k) = Mid(sIn, iBegin, iEnd - iBegin + 1)
            End If
         End If

      Next i

      k = k + 1
      ReDim Preserve sNotRanges(1 To k)
      sNotRanges(k) = Mid(sIn, iEnd + 1)

      For i = 1 To k - 1
         sOut = sOut & sNotRanges(i) & "ДВССЫЛ(""" & sRanges(i) & """)"
      Next i
      sOut = sOut & sNotRanges(k)

      c.FormulaLocal = sOut
   Next c
End Sub

[KL (XL)]

Надеюсь сами формулы или формулы на них ссылающиеся не тяжеловесные, не работают с массивами и не используют функций поиска (ВПР(), ГПР(), ПОИСКПОЗ(), ПРОСМОТР()), условных подсчетов и суммирований (СУММЕСЛИ() СЧЕТЕСЛИ(), СУММПРОИЗВ(), БДСУММ(), БД...) и т.п., а то ДВССЫЛ() может оказаться "медвежьей услугой" из-за своей летучести :-(

Нелетучий эквивалент для =ДВССЫЛ("A1") при условии, что столбцы удаляться не будут: =ИНДЕКС(A:A;1), ну или так на все случаи жизни: =INDEX(1:65536,1,1).

Хотя как бы лекарство не оказалось хуже болезни :-)