Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Замена ВПР и ДВССЫЛ макросом VBA - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Замена ВПР и ДВССЫЛ макросом VBA (Макросы/Sub)
Замена ВПР и ДВССЫЛ макросом VBA
Kamikadze_N Дата: Вторник, 16.08.2016, 08:06 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 42
Репутация: 5 ±
Замечаний: 0% ±

Excel 2010
Добрый день. Есть формула в ячейке
Код
=ВПР($E451;ДВССЫЛ($D451&""&"т");2;ЛОЖЬ)

На листе около 2000 записей, если записей менее 1000 то формула работает корректно, а вот после 1000 записи при внесении новго значения формула перестает работать, более того если кликнуть по ячейке с формулой и нажать Энтер, то данные подгружаются (т.е. формула срабатывает).
Если записей слишком много то формула перестает работать, удаление всех записей в таблице не помогает, формула все равно не работает автоматически, только после ее активации.
Из-за чего я пришел к выводу, что нужно запилить формулу на лист. Но дело в том, что диапазон откуда идет выбор динамический. И Я не могу сообразить Как заставить VBA Именованный диапазон Из столбца D преобразовать в именованный диапазон + символ "т", из из этого преобразованного диапазона произвести выбор ВПР. Думаю нужно использовать что то типо этого
[vba]
Код
Cells(1, 10).FormulaR1C1 = "=VLOOKUP(" & sCr & ",R1C1:R10C2,2,0)"
[/vba] , но вот как заставить выборку проводить по именованному диапазону мозгов не хватает.
К сообщению приложен файл: 111111.xlsm(14Kb)
 
Ответить
СообщениеДобрый день. Есть формула в ячейке
Код
=ВПР($E451;ДВССЫЛ($D451&""&"т");2;ЛОЖЬ)

На листе около 2000 записей, если записей менее 1000 то формула работает корректно, а вот после 1000 записи при внесении новго значения формула перестает работать, более того если кликнуть по ячейке с формулой и нажать Энтер, то данные подгружаются (т.е. формула срабатывает).
Если записей слишком много то формула перестает работать, удаление всех записей в таблице не помогает, формула все равно не работает автоматически, только после ее активации.
Из-за чего я пришел к выводу, что нужно запилить формулу на лист. Но дело в том, что диапазон откуда идет выбор динамический. И Я не могу сообразить Как заставить VBA Именованный диапазон Из столбца D преобразовать в именованный диапазон + символ "т", из из этого преобразованного диапазона произвести выбор ВПР. Думаю нужно использовать что то типо этого
[vba]
Код
Cells(1, 10).FormulaR1C1 = "=VLOOKUP(" & sCr & ",R1C1:R10C2,2,0)"
[/vba] , но вот как заставить выборку проводить по именованному диапазону мозгов не хватает.

Автор - Kamikadze_N
Дата добавления - 16.08.2016 в 08:06
nilem Дата: Вторник, 16.08.2016, 08:34 | Сообщение № 2
Группа: Авторы
Ранг: Ветеран
Сообщений: 1057
Репутация: 400 ±
Замечаний: 0% ±

Excel 2013
может как-то так:
[vba]
Код
Sub ttt()
Dim r As Range
With Range("A2:C" & Cells(Rows.Count, 1).End(xlUp).Row)
    For Each r In .Columns(3).Cells
        r.FormulaR1C1 = "=vlookup(RC2," & r(1, -1).Value & "т" & ",2,0)"
    Next r
End With
End Sub
[/vba]


Яндекс.Деньги 4100159601573
 
Ответить
Сообщениеможет как-то так:
[vba]
Код
Sub ttt()
Dim r As Range
With Range("A2:C" & Cells(Rows.Count, 1).End(xlUp).Row)
    For Each r In .Columns(3).Cells
        r.FormulaR1C1 = "=vlookup(RC2," & r(1, -1).Value & "т" & ",2,0)"
    Next r
End With
End Sub
[/vba]

Автор - nilem
Дата добавления - 16.08.2016 в 08:34
Kamikadze_N Дата: Вторник, 16.08.2016, 08:46 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 42
Репутация: 5 ±
Замечаний: 0% ±

Excel 2010
nilem, спасибо но это не совсем подходит, данная формула заполняет все ячейки только после запуска макроса, можно ли его как то запускать автоматически. Прост когда вносятся данные нужно что бы номера сразу же отображались. А данном примере получается, что сначала заполняешь всю таблицу данными и только потом отображаются номера. Можно как то это форму адаптировать на воркшетс?
[vba]
Код

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("С:С")) Is Nothing Then
[/vba]
Вот как то так запилить, я пробовал но у меня ругается код...
 
Ответить
Сообщениеnilem, спасибо но это не совсем подходит, данная формула заполняет все ячейки только после запуска макроса, можно ли его как то запускать автоматически. Прост когда вносятся данные нужно что бы номера сразу же отображались. А данном примере получается, что сначала заполняешь всю таблицу данными и только потом отображаются номера. Можно как то это форму адаптировать на воркшетс?
[vba]
Код

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("С:С")) Is Nothing Then
[/vba]
Вот как то так запилить, я пробовал но у меня ругается код...

Автор - Kamikadze_N
Дата добавления - 16.08.2016 в 08:46
nilem Дата: Вторник, 16.08.2016, 08:57 | Сообщение № 4
Группа: Авторы
Ранг: Ветеран
Сообщений: 1057
Репутация: 400 ±
Замечаний: 0% ±

Excel 2013
код не ругается, а мягко так намекает, мол, попробуйте по-другому: :)
[vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 2 Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Len(Cells(Target.Row, 1)) * Len(Cells(Target.Row, 2)) Then
    Cells(Target.Row, 3).FormulaR1C1 = "=vlookup(RC2," & Cells(Target.Row, 1).Value & "т" & ",2,0)"
End If
End Sub
[/vba]


Яндекс.Деньги 4100159601573
 
Ответить
Сообщениекод не ругается, а мягко так намекает, мол, попробуйте по-другому: :)
[vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 2 Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Len(Cells(Target.Row, 1)) * Len(Cells(Target.Row, 2)) Then
    Cells(Target.Row, 3).FormulaR1C1 = "=vlookup(RC2," & Cells(Target.Row, 1).Value & "т" & ",2,0)"
End If
End Sub
[/vba]

Автор - nilem
Дата добавления - 16.08.2016 в 08:57
Kamikadze_N Дата: Вторник, 16.08.2016, 09:02 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 42
Репутация: 5 ±
Замечаний: 0% ±

Excel 2010
nilem, Вообще супер то что надо! +100500 к карме!
 
Ответить
Сообщениеnilem, Вообще супер то что надо! +100500 к карме!

Автор - Kamikadze_N
Дата добавления - 16.08.2016 в 09:02
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Замена ВПР и ДВССЫЛ макросом VBA (Макросы/Sub)
Страница 1 из 11
Поиск:

Яндекс цитирования
© 2010-2016 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!