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

Дата: Суббота, 03.02.2024, 19:44 | Сообщение № 1

Группа: Пользователи

Ранг: Новичок

Сообщений: 18

Репутация: 0 ±

Замечаний: 0% ±

Здраствуйте.
Помогите, пожалуйста, исправить.
Номер ячейки искомого значения могу получить только относительной ссылкой.
А таблица с текстом имеет точный диапазон.

[vba]

Код

Sub йцукен()

    Dim Rng As Range, i
    Set Rng = Range("B5:B15")
    For i = Rng.Cells.Count To 1 Step -1
        With Rng.Cells(i)
            If .Value = "break" Then
                .Offset(-1, 2).Formula = "=IFNA(VLOOKUP(R[-4]C[-3],Лист!A1:B10,2,FALSE),"""")"
            End If
        End With
    Next i

End Sub

[/vba]

Вместо

Код

=ЕСНД(ВПР(A4;Лист!A1:B10;2;ЛОЖЬ);"")

Вставляет:
=ЕСНД(ВПР(A4;Лист!'A1':'B10';2;ЛОЖЬ);"")

Спасибо.

К сообщению приложен файл: vstavit_frpmulu.xlsm (24.4 Kb)

Сообщение отредактировал Alex2354 - Суббота, 03.02.2024, 19:47

Дата: Суббота, 03.02.2024, 20:54 | Сообщение № 2

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2563

Репутация: 706 ±

Замечаний: 0% ±

2010

Здравствуйте. Вместо .Formula напишите .FormulaR1C1
И чтобы массив не сползал, формулу надо подправить.

Код

=ЕСНД(ВПР(A11;Лист!$A$1:$B$10;2;ЛОЖЬ);"")

ЯД-41001506838083

Сообщение отредактировал gling - Суббота, 03.02.2024, 21:06

Дата: Суббота, 03.02.2024, 21:04 | Сообщение № 3

Группа: Пользователи

Ранг: Новичок

Сообщений: 18

Репутация: 0 ±

Замечаний: 0% ±

Цитата gling, 03.02.2024 в 20:54, в сообщении № 2 (

)

напишите .FormulaR1C1

Это почти первое, что попробовал. Не получилось. Результат одинаковый.

PS: а вставка значка $ в диапазон выдаёт сразу ошибку.

К сообщению приложен файл: vstavit_formulu2.xlsm (24.6 Kb)

Сообщение отредактировал Alex2354 - Суббота, 03.02.2024, 21:09

Дата: Суббота, 03.02.2024, 21:10 | Сообщение № 4

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2563

Репутация: 706 ±

Замечаний: 0% ±

2010

Мне интересно как вы так умудрились написать формулу, половина в стиле RC? а диапазон листа в стиле А1? Вы уж пишите или так, или так.[vba]

Код

.Offset(-1, 2).FormulaR1C1 = "=IFNA(VLOOKUP(RC[-3],Лист!R1C1:R10C2,2,FALSE),"""")"

[/vba]

ЯД-41001506838083

Сообщение отредактировал gling - Суббота, 03.02.2024, 21:13

Дата: Суббота, 03.02.2024, 21:11 | Сообщение № 5

Группа: Пользователи

Ранг: Новичок

Сообщений: 18

Репутация: 0 ±

Замечаний: 0% ±

Цитата gling, 03.02.2024 в 20:54, в сообщении № 2 (

)

=ЕСНД(ВПР(A11;Лист!$A$1:$B$10;2;ЛОЖЬ);"")

У меня не А11, а переменная ссылка R[-4]C[-6]

Дата: Суббота, 03.02.2024, 21:16 | Сообщение № 6

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2563

Репутация: 706 ±

Замечаний: 0% ±

2010

Вот вытащил из вашего макроса [vba]

Код

If .Value = "break" Then
.Offset(-1, 5).FormulaR1C1 = "=IFNA(VLOOKUP(R[-4]C[-6],Лист!A1:B10,2,FALSE),"""")"
End If

[/vba] Вначале R[-4]C[-6], далее Лист!А1:В10. Должно быть Лист!R1C1:R10C2

ЯД-41001506838083

Сообщение отредактировал gling - Суббота, 03.02.2024, 21:17

Дата: Суббота, 03.02.2024, 21:17 | Сообщение № 7

Группа: Пользователи

Ранг: Новичок

Сообщений: 18

Репутация: 0 ±

Замечаний: 0% ±

Цитата gling, 03.02.2024 в 21:10, в сообщении № 4 (

)

.Offset(-1, 2).FormulaR1C1 = "=IFNA(VLOOKUP(RC[-3],Лист!R1C1:R10C2,2,FALSE),"""")"

Этот вариант вроде работает. Спасибо.

Дата: Суббота, 03.02.2024, 21:41 | Сообщение № 8

Группа: Пользователи

Ранг: Новичок

Сообщений: 18

Репутация: 0 ±

Замечаний: 0% ±

gling, Я дико извиняюсь.
Если это не будет сильной наглостью, то есть простенький вариант это значение после вставки сразу сделать текстовым?

[vba]

Код

Sub ццц()

    Dim Rng As Range, i
    Set Rng = Range("B5:B15")
    For i = Rng.Cells.Count To 1 Step -1
        With Rng.Cells(i)
            If .Value = "break" Then
                .Offset(-1, 2).FormulaR1C1 = "=IFNA(VLOOKUP(R[-4]C[-3],Лист!R1C1:R10C2,2,FALSE),"""")"
            End If
        End With
    Next i

End Sub

[/vba]

К сообщению приложен файл: 2898315.xlsm (24.2 Kb)

Дата: Суббота, 03.02.2024, 22:18 | Сообщение № 9

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2563

Репутация: 706 ±

Замечаний: 0% ±

2010

[vba]

Код

           If .Value = "break" Then
                .Offset(-1, 2).Formula = "=IFNA(VLOOKUP(RC[-3],Лист!R1C1:R10C2,2,FALSE),"""")"
                .Offset(-1, 2).Calculate
                .Offset(-1, 2).Value = .Offset(-1, 2).Value
            End If

[/vba]

ЯД-41001506838083

Дата: Суббота, 03.02.2024, 23:47 | Сообщение № 10

Группа: Пользователи

Ранг: Новичок

Сообщений: 18

Репутация: 0 ±

Замечаний: 0% ±

Огромное спасибо