Блог

Главная - Вопросы по программированию - примените формулу ВПР для переменной количества строк

примените формулу ВПР для переменной количества строк

#vba #excel #vlookup

#vba #excel #ВПР

Вопрос:

У меня есть лист Excel, в котором первые 3 столбца представляют собой сводную таблицу, и всякий раз, когда я ее обновляю, количество строк может меняться. Столбцы E, F, G, H, я использую формулу ВПР на основе столбцов A, B, C.

Поскольку количество строк меняется, как я могу убедиться, что формула ВПР также автоматически настраивается для столбцов E, F, G, H, I на основе количества строк A, B, C?

Прилагается изображение, на котором вы можете видеть, что формула не применялась для последних 3 строк, и я вручную перетаскиваю ячейки, если это сработает. введите описание изображения здесь

Комментарии:

1. @pnuts: как вы можете видеть на изображении для столбца E, он основан на каком-то другом листе с именем «EQ shock sizes», который исправлен. Аналогично для столбцов F, G, H, I

2. Проблема в ссылках на формулы? Или просто формулы не копируются в диапазон E26095: I26098?

3. @MarkBalhoff: Да, проблема в том, что формулы не копируются в диапазон E26095: I26098.

4. @AshokVardhan Как вы обновляете сводную таблицу? VBA? Интерфейс Excel (лента и т. Д.)? Другое ?

5. @MarkBalhoff: он основан на VBA. Поэтому я надеюсь, что смогу включить в нее и часть ВПР.

Ответ №1:

Несколько грубой силой было бы сделать что-то вроде

 ' Just Refreshed Pivot Table
'
' Assumes variable ws refers to this worksheet
' Assumes your formulas start on row 2

ws.Range("E2:I2").Copy ws.Range("E3:E" amp; ws.UsedRange.Rows.Count)

Затем поместите оператор if вокруг вашей формулы в столбце E (и аналогичные операторы if в других столбцах):

 =If(C2="","",VLookup(...))

Вы всегда можете найти более надежный способ определить, сколько строк находится слева или справа. Или выполните следующие действия:

 For i = 1 to ws.UsedRange.Rows.Count
    If(ws.Cells(i,3).Value = "")
        ws.Range("E" amp; i amp; ":I" amp; i).ClearContents
    Else
        '''  Apply your formulas / values in columns E through I of row i
    End If
Next i
Метки: