Автоматическая замена формулы на ее результат при соблюдении условия

[KWYJIBO]

Здравствуйте уважаемые мастера программистского спорта!
Мой вопрос ранее уже задавался кем-то на форуме, но то были либо частные варианты вопроса, либо ответы были чуть-чуть не те, что хотелось бы.

Несколько дней голову ломаю над этой математическо-логической задачей

Допустим в колонках A и B есть табличка 2x5 - то есть два столбца и пять строк.
Как сделать следующее: заполняя любую ячейку первого столбца текстом, напротив нее, во втором столбце, появляется цифра 1. (с одной стороны это легко, но формулой =если(А2="";"";1) тут не обойтись, читаем далее). Далее, заполняя любую пустую ячейку первого столбца текстом, напротив нее, во втором столбце должна появиться уже цифра 2.
Далее из пяти ячеек первого столбца таблицы у нас пустыми остается три. Заранее не известно какую из них мы будем заполнять, но, можно догадаться, что при заполнении одной из них, напротив заполняемой текстом ячейки, напротив нее, во втором столбце, должна появиться цифра три. И так далее, до тех пор, пока вся таблица не будет заполнена.
Фишка в том, что порядковые цифры 1-5 в колонке B должны появляться автоматически, как только соседние ячейки из колонки А перестанут быть пустыми.

максимум до чего я продвинулся, это создать кнопку и записать макрос копирования формулы =если(А2="";"";1) и вставки в ту же ячейку уже её результата.
но в этом случае придется каждый раз...
а) нажимать кнопку, после заполнения текстом ячеек первого стобца
б) записывать макросы и создавать кнопки в количестве, равном количеству строк в таблице (это в примере я привел 5 строк, а в действительности их будут сотни)
А это уже как то не тянет на красивое решение математическо-логической задачи.

Подскажите пожалуйста как разрешить эту задачу, а если выложите еще и наглядный пример, то я Вам памятник воздвигну нерукотворный!

Заранее спасибо за внимание к вопросу и уделение времени на его решение.

[IgorGO]

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rg0 As Range, c As Range
  Set rg0 = [a1:a5]
  If Not Application.Intersect(rg0, Target) Is Nothing Then
    If Target = "" Then
      If Target.Offset(0, 1) = "" Then Exit Sub
      Application.EnableEvents = False
      For Each c In rg0.Offset(0, 1)
        If c > Target.Offset(0, 1) Then c = c - 1
      Next
      Target.Offset(0, 1).ClearContents
      Application.EnableEvents = True
    Else
      If Target.Offset(0, 1) <> "" Then Exit Sub
      Target.Offset(0, 1) = WorksheetFunction.Max(rg0.Offset(0, 1)) + 1
    End If
  End If
End Sub

здесь Set rg0 = [a1:a5] - диапазон ячеек, за изменением которых следит программа

[Казанский]

Осталось придумать, что делать, если изменяются сразу несколько ячеек из А1:А5

[KWYJIBO]

Объявляется благодарность мастеру спорта международного класса IgorGO который за пять секунд решил задачу, решение которой было достойно Филдсовской премии!

Вот Человек с большой буквы! Мало того что решил задачу, так еще и сделал это так быстро, что я даже еще свой вопрос до конца не задал, а решение уже было готово (немножко утрирую, но только немножко)

IgorGO, спасибо тебе большое!

http://img2.ntv.ru/home/news/20080114/hands_std.jpg

Восхищён и благодарен!

[KWYJIBO]

Программа от IgorGO работает на ура!
Но, прошу у него прощения, ибо в постановке вопроса, ничтоже сумняшеся, допустил ошибку.
Я сказал что в колонке А ячейки будут заполняться текстом.
Но, в действительности в колонке А уже будут формулы, со ссылками на другие ячейки, расположенные вообще в разных колонках, проще объяснить на примере
Формулы в ячейках колонки А будут одинаковые:
А1: =если(D1="";"";1)
A2: =если(G1="";"";1)
A3: =если(J1="";"";1)
A4: =если(M1="";"";1)
A5: =если(P1="";"";1)

то есть текст будет вбиваться вообще в другие ячейки, а в колонке А будет информация, пустые ли те ячейки или нет. Если пустые, то и в колонке А они будут пустые. А вот если не пустые, то в нужной ячейке колонки А будет цифра 1.


А далее уже задача повторяется. Если, допустим, сначала текстом заполнили G1, то тогда в А2 появилась единичка, соответственно в B2 будет 1.
Далее, допустим, заполнили текстом М1, соответственно единичка появилась в А4, тогда в B4 будет уже 2.
И так далее.


Полагаю изменение решения будет в том, что определяющим фактором будет не ЗАПОЛНЕНИЕ текстом ячеек колонки А, а появление в них цифры 1.

Программа наверное не сильно должна измениться? Вчера весь вечер пытался разобраться самостоятельно, но не понимаю ничего. Помогите пожалуйста, о гуру.

[IgorGO]

то что было раньше нужно закомментировать или удалить
и скопировать в модуль листа это (отследим теперь событие "пересчет"):

Private Sub Worksheet_Calculate()
  Dim rg As Range, c As Range, rg0 As Range, i As Long
  Set rg0 = [a1:a5]
  If WorksheetFunction.Count(rg0) = WorksheetFunction.Count(rg0.Offset(0, 1)) Then Exit Sub
  For Each rg In rg0.Cells
    If rg = "" Then rg.Offset(0, 1).ClearContents Else _
      If rg.Offset(0, 1) = "" Then rg.Offset(0, 1) = WorksheetFunction.Max(rg0.Offset(0, 1)) + 1
  Next
  For Each rg In rg0.Cells
    If rg.Offset(0, 1) > 1 Then
      If WorksheetFunction.CountIf(rg0.Offset(0, 1), rg.Offset(0, 1) - 1) = 0 Then rg.Offset(0, 1) = rg.Offset(0, 1) - 1
    End If
  Next
End Sub

[IgorGO]

а если последний цикл вот так записать:

  For Each rg In rg0.Cells
    If rg.Offset(0, 1) > 1 Then
      If WorksheetFunction.CountIf(rg0.Offset(0, 1), "<" & rg.Offset(0, 1)) < rg.Offset(0, 1) - 1 _
        Then rg.Offset(0, 1) = rg.Offset(0, 1) - 1
    End If
  Next

тогда вообще все будет правильно работать!!!

[KWYJIBO]

Гениально!
https://vk.com/video?gid=60620017&z=...0017_170342798

Игорь, буду откровенен, я вообще ни капли не понял из того, что Вы написали, но всё работает просто идеально! На четвертом десятке лет, к сожалению, я так и не освоил макросы, буду учиться на таких великолепных примерах, которые имею честь видеть, благодаря Вашей помощи, какое счастье, что я попал к вам на этот форум.

Спасибо большое, Игорь, Вы мне очень помогли!!