Новости:

Теперь на форум можно залогиниться / зарегистрироваться с помощью ВКонтакте. Уже существующие пользователи могут связать свою учетную запись с аккаунтом ВКонтакте одним кликом в профиле пользователя http://forum.msexcel.ru/index.php?action=profile;area=account

Главное меню

Получение значение формулы без записи ее в ячейку

Автор lsg, 23.05.2011, 12:04

« назад - далее »

lsg

Уважаемые форумчане.
Помогите мне, пожалуйста, в решении следующего вопроса.

Мне нужно получить значение формулы без записи формулы в ячейку. Сейчас я записываю в ячейку формулу, а потом забираю ее как значение переменной «Всего».
   Range("K5").FormulaLocal = "=ВПР(D5";G6:K10;5;1)"
   Всего = Range("K5")
Где-то совсем недавно видел подобное решение, но, к сожалению, не могу найти.


_Boroda_

Скажи мне, кудесник, любимец ба'гов...



Яндекс-деньги: 41001632713405
Webmoney: R289877159277; Z102172301748; E177867141995

lsg


machodg

Здравствуйте Уважаемый _Boroda_

А как привести к подобному виду такой код с формулами:

Cells(i, 2) = "=SUMIF('Deficit1Mc'!R5C1:R" & k & "C1,RC[-1],'Deficit1Mc'!R5C3:R" & k & "C3)"

Cells(i, 5).FormulaArray = "=LARGE(IF(RC[-4]='Deficit1Mc'!R5C1:R" & k & "C1,IF('Deficit1Mc'!R5C3:R" & k & "C3<>0,'Deficit1Mc'!R5C2:R" & k & "C2)),1)"

Cells(i, 7).FormulaArray = "=SUM(IF('Deficit1Mc'!R5C1:R" & k & "C1=MokleVada2!RC[-6],IF('Deficit1Mc'!R5C2:R" & k & "C2=MokleVada2!RC[-1],'Deficit1Mc'!R5C3:R" & k & "C3)))"

Cells(i, 9).FormulaArray = "=TRUNC(((0&1&""/""&(IF(RIGHT(RC[-2],2)="".1"",""10"",(RIGHT(RC[-2],2))))&""/""&LEFT(RC[-2],4))-TODAY())/30.4)"

Я попытался, но не вышло. Видимо не понял синтаксис WorksheetFunktion.
Заранее благодарен за ответ

machodg

Извините за опоздание с ответом.
Файл прилагаю.
В нем макрос в котором хотел использовать ваш метод WorksheetFunktion, но сам не смог.
Надеюсь с Вашей помощью разберусь.
Спасибо за проявленное внимане

machodg

Уважаемые профессионалы.

Мне всего лишь надо разобраться с принципом WorksheetFunction.
Применимо ли WorksheetFunction  к любой произвольной формуле?
Каков синтаксис при использовании ссылки R1C1 и переменных в адресах.
Хотя бы один сложный пример.
В остальном я сам разберусь.

Буду всем очень признателен за ответ






machodg


machodg

Уважаемые профессионалы!
Ниже приведенный код записывает значение формулы без записи самой формулы в ячейку C5 и D5 дипазона переменной длины.

r1 = Sheets("Sheet1").Range("B5").End(xlDown).Row                       '(r1=348)

Cells(5, 3) = WorksheetFunction.SumIf(Range("Sheet1!$A$5:$A$" & r1), [$A5], Range("Sheet1!$D$5:$D$" & r1))      '(1)

Cells(5, 4) = WorksheetFunction.Sum([IF(Sheet1!$A$5:$A$348=MokleVada2!$A5,IF(Sheet1!$B$5:$B$348=Sheet2!$F5,_
                  Sheet1!$C$5:$C$348))])                                                                                                     '(2)
Но у меня никак не получется во вторую конструкцию (2) для ячейки D5 вставить переменную типа r1 для конечного адреса диапазона.
Подскажите пожалуйста, как это сделать?
Заранее благодарен за ответ.

kuklp

Ну, каков вопрос, таков и ответ. Используйте  evaluate. Не спроможетесь самостоятельно - кладите пример.
П.С. кажись, Вашу формулу можно упростить. Выложите в файле, пусть формулисты посмотрят.
Я, как всегда, чертовски адекватен... Email: pilipnikop@yandex.ua WM Z206653985942, R334086032478, U238399322728, E332314026771

machodg

Вы правы. Вопрс сумбурный и с ошибкой (писал на голодный желудок в конце рабочего дня). Приношу свои извинения.

Смысл вопроса: как исправить код, который работает без проблем

Cells(5, 4) = WorksheetFunction.Sum([IF(Sheet1!$A$5:$A$348=Sheet2!A5,IF(Sheet1!$B$5:$B$348=Sheet2!$F5,Sheet1!$C$5:$C_$348))])
чтобы он работал с переменной r1

r1 = Sheets("Sheet1").Range("B5").End(xlDown).Row
Cells(5, 4) = WorksheetFunction.Sum([IF(Sheet1!$A$5:$A$r1=Sheet2!A5,IF(Sheet1!$B$5:$B$r1=Sheet2!$F5,Sheet1!$C$5:$C$r1))])

Пример во вложении

kuklp

#10
Ну как и обещал, смотрите. Дальше по образцу и без нас обойдетесь:-)

П.С. "кажись, Вашу формулу можно упростить" так и не выложили:-( Мне пришлось из макроса вникать, а я не формулист, тяжело.
Я, как всегда, чертовски адекватен... Email: pilipnikop@yandex.ua WM Z206653985942, R334086032478, U238399322728, E332314026771

machodg

Огромное спасибо!!
Во всем разобрался
Использую этот код в цикле:

Sub EXP()
    Sheets("Sheet2").Select
    r1 = Sheets("Sheet1").Cells(5, 1).End(xlDown).Row
    r2 = Sheets("Sheet2").Cells(5, 1).End(xlDown).Row
    With Sheets("Sheet1"): Set c = .Range(.[A5], .[A5].End(xlDown)): End With
   
    For i = 5 To r2 Step 1
      Cells(i, 3) = WorksheetFunction.SumIf(Range("Sheet1!$A$5:$A$" & r1), Evaluate("$A" & i), Range("Sheet1!$D$5:$D$" & r1))
      a = Evaluate("IF(Sheet1!" & c.Address & "=Sheet2!A" & CStr(i) & ",IF(Sheet1!" & c.Offset(, 1).Address & "=Sheet2!$F" & _
           CStr(i) & ",Sheet1!" & c.Offset(, 2).Address & "))")
      Cells(i, 4) = Application.Sum(a)
    Next i
   
End Sub

Работает прекрасно
Желаю всем удачи

lsg

Цитата: _Boroda_ от 23.05.2011, 13:31
Всего = WorksheetFunction.VLookup([D5], [G6:K10], 5, 1)

Уважаемый _Boroda_.
Вернусь к своему вопросу. А можно ли в этой формуле вместо ссылки на ячеку D5 подставить переменную?. Попробовал сделать так,
WorksheetFunction.VLookup(ТекущаяДата, [G6:K10], 5, 1)

Но макрос выдает ошибку: "Run-time error '1004' Невозможно получить свойство VLookup класса WorksheetFunction".
Спасибо.

lsg

Цитата: lsg от 09.12.2011, 09:32
Цитата: _Boroda_ от 23.05.2011, 13:31
Всего = WorksheetFunction.VLookup([D5], [G6:K10], 5, 1)

Уважаемый _Boroda_.
Вернусь к своему вопросу. А можно ли в этой формуле вместо ссылки на ячеку D5 подставить переменную?. Попробовал сделать так,
WorksheetFunction.VLookup(ТекущаяДата, [G6:K10], 5, 1)

Но макрос выдает ошибку: "Run-time error '1004' Невозможно получить свойство VLookup класса WorksheetFunction".
Спасибо.

Хочу добавить, что это переменная типа Дата. А формула ищет по дате в заданном диапазоне  определенное значение.
Спасибо

lsg