Страницы: 1
RSS
Как записать формулу в макросе используя свойство Cells
 
Здравствуйте уважаемые форумчане.
Подскажите пожалуйста, как можно преобразовать вот такую формулу:
ActiveCell.FormulaR1C1 = "=IFERROR(IF(RC1*RC2*RC171=0,0,1),0)
с использованием обозначения ячеек свойством Cells?

Результат представлял себе примерно как-то так, но только, чтобы работало)):

ActiveCell.Formula = "=IFERROR(IF((Cells(1,1))*(Cells(1,2))*(Cells(1,lcol))=0,0,1),0)"

Запрос продиктован тем, что последний множитель формулы будет находится в последнем столбце таблицы, которая будет ежедневно меняться.

*lcol = Cells(1, Columns.Count).End(xlToLeft).Column

Заранее благодарю.
 
Код
lcol = Cells(1, Columns.Count).End(xlToLeft).Column
ActiveCell.FormulaR1C1 = "=IFERROR(IF(RC1*RC2*RC" & lcol & "=0,0,1),0)"
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
Игорь, к сожалению не получается((
Нельзя ли RC1 (и другие) представить в виде Cells(1;1)?

Чтобы получить в итоге что-то типо: "=IFERROR(IF((Cells(1,1))*(Cells(1,2))*(Cells(1,lcol))=0,0,1),0)" ?


Извиняюсь, все работает - неправильно подтянул ссылку на ячейки в итоговом файл.
Игорь, большое спасибо.
Изменено: Дмитрий - 28.06.2022 13:35:55 (Неправильно вставил ссылку)
 
Тем не менее вопрос актуален - в макросах вообще возможно написание формул через Cells?
 
дело не в том, что можно написать в макросе, а дело в том воспримет-ли Excel как корректную формулу, тот текст, что вы набрали макросом и пытаетесь подсунуть ему как формулу
забыли одну скобку закрыть для VBA по барабану сколько там скобок отрыто и закрыто внутри текстовой строки, а Excel нужно понять что это за формула
и что такое Cells(9,7) он не знает, зато знает что за ячейка R9C7

напишите функцию
Function RCAdr(r,c) as string
 RCAdr = "R" & r & "C" & c
End Function

использовать теперь RCAdr(9,7)
практически так же легко и компактно как использовать Cells(9,7)
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
Цитата
Дмитрий написал:
Игорь, к сожалению не получается((
Не получается что? Приложите файл с неполучалкой
Скажи мне, кудесник, любимец ба’гов...
 
Цитата
Дмитрий: Как записать формулу в макросе используя свойство Cells
напрямую никак
Обходные пути, в том числе и полный аналог указания Cells() вам Ігор Гончаренко уже показал
Изменено: Jack Famous - 28.06.2022 14:49:00
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Цитата
Дмитрий написал:
в макросах вообще возможно написание формул через Cells?
при наличии переводчика -  вполне))
а вот и он:
Код
Function ReplaceCells$(F$, ParamArray pr())
  Const pt$ = "Cells\(([0-9]+|[a-z]+),([0-9]+|[a-z]+)\)"
  Dim re, i&, j&, p&, ms, rs$, s$
  Set re = CreateObject("VBScript.RegExp"): re.Global = True
  re.Pattern = pt
  If re.Test(F) Then
    Set ms = re.Execute(F): re.Pattern = "[a-z]+"
    For i = 0 To ms.Count - 1
      s = "R"
      If re.Test(ms(i).submatches(0)) Then s = s & pr(p): p = p + 1 _
        Else s = s & ms(i).submatches(0)
      s = s & "C"
      If re.Test(ms(i).submatches(1)) Then s = s & pr(p): p = p + 1 _
        Else s = s & ms(i).submatches(1)
      F = Replace(F, ms(i), s): s = ""
    Next
    ReplaceCells = F
  End If
End Function

выполните  Test
Код
Sub Test()
  Dim s$, lcol
  lcol = Cells(1, Columns.Count).End(xlToLeft).Column
  s = "=IFERROR(IF(Cells(12,1)*Cells(100,234)*Cells(1,lcol)=0,0,1),0)"
  Debug.Print s
  Debug.Print ReplaceCells(s, lcol)
End Sub

смотрите в окне Immediate  как ReplaceCells преобразовала вашу запись с Cellsами в R1C1 формулу
если некоторые №№ строк, колонок являются не константами, а вычисляемыми значениями - передавайте их в ReplaceCells в том порядке и количестве, в котором они встречаются в вашей записи)
Изменено: Ігор Гончаренко - 28.06.2022 15:08:25
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
Большое всем спасибо.

Игорь, как написал раньше - самый первый Ваш ответ мне очень помог и исправил мой файл.

Насчет обходного пути с написанием функции - буду пробовать.

Касательно переводчика - для меня это пока высокая материя, честно - вообще не понимаю код и как он работает, нужно разбираться...

Еще раз БОЛЬШОЕ спасибо!
 
в коде не обязательно разбираться, достаточно понять как им пользоваться
(пример вам показал)

относительно вашего первого поста все может выглядеть так:
Код
lcol = Cells(1, Columns.Count).End(xlToLeft).Column
ActiveCell.Formula = ReplaceCells("=IFERROR(IF((Cells(1,1))*(Cells(1,2))*(Cells(1,lcol))=0,0,1),0)", lcol)

при этом не важно поняли вы как работает ReplaceCells или нет, главное - она впихнула в активную ячейку нужную формулу правильно преобразовав ваши Cells в R1C1 адреса ячеек
и на вопрос "можно-ли написать формулу с использованием Cells?" теперь есть положительный ответ
Изменено: Ігор Гончаренко - 28.06.2022 15:38:33
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
Цитата
Дмитрий написал:
в макросах вообще возможно написание формул через Cells?
- можно использовать адрес ячейки с cells.
Что-то типа:
Код
 "=IFERROR(IF((" & Cells(1,1).address & ")*(" & Cells(1,2).address & ")*(" & Cells(1,lcol).address & ")=0,0,1),0)" 
 
Hugo, в таком случае нужно обязательно указывать параметры Address и/или принудительно использовать FormulaLocal/FormulaR1C1

Ну и ReplaceCells быстрее будет, т.к. работает с координатами напрямую, а можно ещё ускорить, заменив регулярки строковыми  :)
Изменено: Jack Famous - 28.06.2022 17:39:00
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Коллеги, добрый день
немного более сложная формула, и методики, предложенные здесь, не срабатывают
пробовал всё от Iгор Гончаренко

во вложении пример - таблица с продуктами, количества, цены
задача - расчет средневзвешенной цены по каждому продукту
то есть справа формула СУММПРОИЗВ/СУММ, она должна была ехать по столбцу и, опираясь на переменные, менять размер диапазона
для картофеля свой диапазон
для морковки свой
и т.д.

и вот не получается заменить этими переменными в формуле стиля RC
ни через метод Address
ни через Replace
и никак вообще - ошибки в коде, не понимает двоеточие, закрыть выражение требует и миллион других причин

буду признателен за подсказки
 
Если уж макросы используете, то может проще эту формулу заменить UDF?
Код
Function СРВЗВЦЕНА(iTbl As Range, iName As Range, nmClmn As Integer, costClmn As Integer, countClmn As Integer)
'iTbl - вся таблица с данными
'iName - наименование продукции, по которому ищем значение (ссылка на ячейку)
'nmClmn - номер столбца с Наименованием
'costClmn - номер столбца с Ценой
'countClmn - номер столбца с Количеством
'номера столбцов задаются относительно Таблицы, а не Листа!
Dim arr()
arr = iTbl.Value
With CreateObject("Scripting.Dictionary")
  For I = LBound(arr, nmClmn) To UBound(arr, nmClmn)
    If arr(I, nmClmn) = iName.Value Then
      If Not .Exists(arr(I, nmClmn)) Then
        .Add arr(I, nmClmn), (arr(I, costClmn) * arr(I, countClmn))
      Else
        .Item(arr(I, nmClmn)) = .Item(arr(I, 1)) + arr(I, costClmn) * arr(I, countClmn)
      End If
      iSum = iSum + arr(I, countClmn)
    End If
  Next
  СРВЗВЦЕНА = .Item(iName.Value) / iSum
End With
End Function
Согласие есть продукт при полном непротивлении сторон.

Контакты, благодарности
 
Sanja,

да, спасибо!
не знаю, что за UDF, но эта штука реально сработала)
 
UDF (User Defined Function)
Согласие есть продукт при полном непротивлении сторон.

Контакты, благодарности
 
Цитата
написал:
UDF (User Defined Function)
благодарю, надо поизучать
по ходу, полезная штука  
Страницы: 1
Наверх