Страницы: 1
RSS
Не получается формулу в макросе изменить на динамическую
 
Доброго времени суток!
Можете помочь в написании макроса
В моем макросе я дал переменные к двум файлам.

Private sh_ассигнование As Workbook
Private sh_погашение As Workbook

Код
Private sh_ассигнование As Workbook
Private sh_погашение As Workbook
Private end1 As Long

Sub NPL_ФЛ_Рестр() 'Main макрос

Call обзор_файла1 'Открывает окно где нужно выбрать файл 1
Call обзор_файла2 'Открывает окно где нужно выбрать файл 2
Call copypaste

End Sub

Private Sub обзор_файла1()
result = Application.GetOpenFilename(MyFilter, , "Открой Ассигнование по резервам", "открой")
If result = "False" Then
    Kill_all = True
    Exit Sub
End If
Workbooks.Open Filename:=result, ReadOnly:=1
Set sh_ассигнование = Workbooks(Dir(result))  'Тут дал переменный на workbook 1
End Sub

Private Sub обзор_файла2()

result = Application.GetOpenFilename(MyFilter, , "Открой КП по NPL.Погашение", "открой")
If result = "False" Then
    Kill_all = True
    Exit Sub
End If
Workbooks.Open Filename:=result, ReadOnly:=1
Set sh_погашение = Workbooks(Dir(result))   'Тут дал переменный на workbook 2
'    Скрывает все действия записи макроса
Application.Wait Now + TimeValue("00:00:01")
    Application.ScreenUpdating = False
End Sub

'Тут только часть кода написал сюда, так как важен только формула в макросе
'Вставляем суммы наших пулов из листа "Страница1_1" в Ассигнование по резервам
'Формулы статистические, нужно сделать динамический, связать формулу как то с (sh_ассигнование As Workbook), (sh_погашение As Workbook) 

sh_погашение.Sheets("КП по NPL ФЛ").Range("I8").FormulaR1C1 = "=SUMIFS('[Ассигнования по резервам (01.09.2019 - 23.09.2019).xlsx]Страница1_1'!C25,'[Ассигнования по резервам (01.09.2019 - 23.09.2019).xlsx]Страница1_1'!C18,""ПОТРЕБЦЕЛИ"",'[Ассигнования по резервам (01.09.2019 - 23.09.2019).xlsx]Страница1_1'!C28,0)"

sh_погашение.Sheets("КП по NPL ФЛ").Range("I15").FormulaR1C1 = "=SUMIFS('[Ассигнования по резервам (01.09.2019 - 23.09.2019).xlsx]Страница1_1'!C25,'[Ассигнования по резервам (01.09.2019 - 23.09.2019).xlsx]Страница1_1'!C18,""АВТОКРЕДИТ"",'[Ассигнования по резервам (01.09.2019 - 23.09.2019).xlsx]Страница1_1'!C28,0)"

sh_погашение.Sheets("КП по NPL ФЛ").Range("I22").FormulaR1C1 = "=SUMIFS('[Ассигнования по резервам (01.09.2019 - 23.09.2019).xlsx]Страница1_1'!C25,'[Ассигнования по резервам (01.09.2019 - 23.09.2019).xlsx]Страница1_1'!C18,""ИПОТЕКА"",'[Ассигнования по резервам (01.09.2019 - 23.09.2019).xlsx]Страница1_1'!C28,0)"

sh_погашение.Sheets("КП по NPL ФЛ").Range("I29").FormulaR1C1 = "=SUMIFS('[Ассигнования по резервам (01.09.2019 - 23.09.2019).xlsx]Страница1_1'!C25,'[Ассигнования по резервам (01.09.2019 - 23.09.2019).xlsx]Страница1_1'!C18,""БЕЗЗАЛОГОВЫЕ"",'[Ассигнования по резервам (01.09.2019 - 23.09.2019).xlsx]Страница1_1'!C28,0)"

sh_погашение.Sheets("КП по NPL ФЛ").Range("I36").FormulaR1C1 = "=SUMIFS('[Ассигнования по резервам (01.09.2019 - 23.09.2019).xlsx]Страница1_1'!C25,'[Ассигнования по резервам (01.09.2019 - 23.09.2019).xlsx]Страница1_1'!C18,""БЫСТРДЕН"",'[Ассигнования по резервам (01.09.2019 - 23.09.2019).xlsx]Страница1_1'!C28,0)"

sh_погашение.Sheets("КП по NPL ФЛ").Range("I43").FormulaR1C1 = "=SUMIFS('[Ассигнования по резервам (01.09.2019 - 23.09.2019).xlsx]Страница1_1'!C25,'[Ассигнования по резервам (01.09.2019 - 23.09.2019).xlsx]Страница1_1'!C18,""КРЕДИТНЫЕКАРТЫ"",'[Ассигнования по резервам (01.09.2019 - 23.09.2019).xlsx]Страница1_1'!C28,0)"

sh_погашение.Sheets("КП по NPL ФЛ").Range("I50").FormulaR1C1 = "=SUMIFS('[Ассигнования по резервам (01.09.2019 - 23.09.2019).xlsx]Страница1_1'!C25,'[Ассигнования по резервам (01.09.2019 - 23.09.2019).xlsx]Страница1_1'!C18,""КРЕДИТЫКИК"",'[Ассигнования по резервам (01.09.2019 - 23.09.2019).xlsx]Страница1_1'!C28,0)"

Формула = *=СУММЕСЛИМН('[Ассигнования по резервам (01.09.2019 - 23.09.2019).xlsx]Страница1_1'!$Y:$Y;'[Ассигнования по резервам (01.09.2019 - 23.09.2019).xlsx]Страница1_1'!$R:$R;"ПОТРЕБЦЕЛИ";'[Ассигнования по резервам (01.09.2019 - 23.09.2019).xlsx]Страница1_1'!$AB:$AB;1)*

Нужно сделать формулу динамическим в макросе чтобы он взял данные с файла, который мы открываем через путь
 
Чтобы формула использовала переменное значение имени книги (допустим), надо сформировать строку из постоянной части формулы и переменной части (там, где будет переменное значение имени книги).
Для простоты возьмем случай, когда ранее в переменную sh_ассигнование запихнули некую книгу.
И теперь хотим макросом в ячейку А1 активного листа вставить формулу - ссылку на ячейку С5 Листа1 книги sh_ассигнование,:
Код
Range("A1").Formula= "=" & sh_ассигнование.Name & "Лист1!C5"
'объединяя знак "=",  значение свойства переменной и СТРОКУ С5 через знак ампресанда получаем строку, которую потом пихаем в формулу.
'Еще пример. Допустим хотим получить в ячейке А4 активного листа сумму диапазона ячеек С2:С4 с листа 1 переменной книги. 
'Причем хотим получить формулу в локальном исполнении (СУММ): 
Range("A4").FormulaLocal= "=СУММ(" & sh_ассигнование.Name & "Лист1!C2:C4)"

Надеюсь, теперь более понятно, что надо изменить в вашем макросе для достижения нужного результата. :)
Кому решение нужно - тот пример и рисует.
 
другой файл для макроса
Страницы: 1
Наверх