#excel #vba
#excel #vba
Вопрос:
У меня есть поиск, который возвращает формулу в виде текста в зависимости от значения конкретной ячейки.
Возвращаемая формула (в виде текста) выглядит следующим образом:
SUM(INDIRECT("AF"amp;row()),INDIRECT("AG"amp;row()))
Затем я использую EVALUATE()
функцию для вычисления формулы, но получаю ошибку #REF из-за использования INDIRECT()
.
Эта страница поддержки Microsoft была единственной ссылкой, которую я смог найти для этой конкретной проблемы, и, похоже, она не предлагает подходящего решения.
Как я могу: а) реструктурировать формулу, чтобы избежать использования INDIRECT
, или б) заставить EVALUATE()
играть красиво?
Редактировать
Таблица поиска выглядит примерно так:
Введите СУММУ (КОСВЕННУЮ («AF» amp; row()), КОСВЕННУЮ («AG» amp; row()))
Введите сумму B (КОСВЕННУЮ («AF» amp; row()), КОСВЕННУЮ («AG» amp; row()), КОСВЕННУЮ («AH» amp; row()))
На другом листе содержимое ячеек B1: 200 имеет либо тип A, либо тип B. Поиск возвращает строку формулы на основе значения ячейки и помещает ее в Bx. Затем я оцениваю строку, чтобы получить результат.
Комментарии:
1. Вы используете evaluate() как часть макроса XLM или в ячейке рабочего листа?
2. На самом деле я использую ее в именованном диапазоне, который затем вызываю из ячейки.
3. Можете ли вы показать поиск? Неясно, каков контекст для row() . Я не уверен, что вам нужны как косвенные, так и оценочные, поскольку они оба служат одной и той же цели…
4. Возможно, это
ROW
возвращает массив на основе его аргумента? То естьROW()
возвращает одноэлементный массив с текущим номером выбранной строки.ROW(a42:q99)
возвращает {42; 43; …; 99}, и т.д. Просто комментарий, поскольку у меня нет Excel, чтобы возиться с этим…5. @Tim, пожалуйста, посмотрите мою правку с таблицей поиска. INDIRECT не вычисляет формулу, а только интерпретирует текст как ссылку на ячейку. Вот почему мне нужны оба.
Ответ №1:
ОК, 3-я попытка ответа:
спасибо за пример: сбой, потому что вы пытаетесь использовать INDIRECT для вычисления именованной формулы, а INDIRECT обрабатывает только ссылки, а не формулы.
Вместо этого вам нужно использовать EVALUATE , но встроенной функции EVALUATE worksheet нет (функция EVALUATE, которую вы используете в определенном имени, является древней макрофункцией XLM).
Я предлагаю вам вместо этого использовать мой EVAL VBA UDF
Public Function EVAL(theInput As Variant) As Variant
'
' if UDF evaluate the input string as though it was on this sheet
' else evaluate for activesheet
'
Dim vEval As Variant
Application.Volatile
On Error GoTo funcfail
If not IsEmpty(theInput) then
If TypeOf Application.Caller.Parent Is Worksheet Then
vEval = Application.Caller.Parent.Evaluate(Cstr(theInput))
Else
vEval = Application.Evaluate(cstr(theInput))
End If
If IsError(vEval) Then
EVAL = CVErr(xlErrValue)
Else
EVAL = vEval
End If
End If
Exit Function
funcfail:
EVAL = CVErr(xlErrNA)
End Function
а затем используйте определенные имена с относительными ссылками, такими как TypeA=SUM(Лист3!RC1,ЛИСТ3RC2).
Есть некоторые «причуды» ОЦЕНКИ, о которых вы должны знать: см.
http://www.decisionmodels.com/calcsecretsh.htm
Комментарии:
1. Похоже, это самый близкий результат к тому, к чему я пытался добраться. Большое спасибо за вашу помощь и ваше терпение.
Ответ №2:
Если я понимаю, что вы пытаетесь сделать правильно, почему бы не получить результат непосредственно из ячейки..
Я не думаю, что вам нужен косвенный, потому что вам просто нужен текст формулы
(хотя я не уверен, что должна делать СТРОКА ())
SUM («AF» amp; row (), «AG» amp; row ())
Комментарии:
1. Чарльз, row() получает ссылку на текущую строку. К сожалению, такое построение суммы не сработает, потому что для интерпретации вашего текста как ячейки требуется КОСВЕННОЕ значение. Это разница между =SUM(«AF2″,»AG2»), которая не работает, и =SUM(AF2,AG2), которая работает.
Ответ №3:
Если вы просто хотите использовать определенное имя для добавления столбцов AF и AG в текущую строку, тогда просто используйте относительные ссылки (проще определить в режиме R1C1, затем переключитесь обратно на A1). Определенное имя и сумма имеют формулу Refersto из =SUM(RC32,RC33)
Затем, где бы вы ни использовали AndySum в формуле, она добавит содержимое столбцов AF и AG в эту строку.
Комментарии:
1. Понятно, но не всегда нужно добавлять AF и AG. Я отредактировал вопрос в соответствии с просьбой Тима, но это может помочь лучше объяснить, чего я пытаюсь достичь. Спасибо за ваше терпение.
2. Так почему бы не использовать формулу поиска с IF или ВЫБРАТЬ выбор между различными вариантами определенных имен
3. Потому что у меня может быть пара сотен типов в моей таблице поиска, что приведет к паре сотен определенных имен и сверхдлинной формуле IF…
Ответ №4:
Чтобы расширить ответ Чарльза: эта функция позаботится о поиске и вычислит номер строки. Я предполагаю, что формула (в ваших примерах) предназначена только для суммирования определенных значений из той же строки, что и ячейка, возвращающая конечный результат.
Моя таблица поиска выглядит следующим образом:
TypeA SUM(B<r> ,D<r>)
TypeB SUM(C<r>,D<r>)
UDF:
Public Function GetAndRunCalc(CalcType As String)
Application.Volatile
Dim ac As Object, rw, f
On Error GoTo haveError
If TypeOf Application.Caller.Parent Is Worksheet Then
Set ac = Application.Caller
rw = ac.Parent.Range(ac.Address).Row
'adjust for your lookup table...
f = Application.VLookup(CalcType, Sheet1.Range("B4:C5"), 2, False)
If Not IsError(f) Then
f = Replace(f, "<r>", rw)
GetAndRunCalc = ac.Parent.Evaluate(f)
Else
GetAndRunCalc = "Type??"
End If
Else
GetAndRunCalc = "Must be called from worksheet cell"
End If
Exit Function
haveError:
GetAndRunCalc = CVErr(xlErrValue)
End Function