0

My Goal would be to control a formula from the first worksheet with this code on the second worksheet (same workbook).

Dim ERP As Worksheet
Set ERP = ThisWorkbook.Worksheets(2)


ERP.Activate
ERP.Range("O1").Value = "Korositás intervallum"

ERP.Range("O2").Activate
ActiveCell.FormulaR1C1 = _
    "=IFS(RC[-1]<=0,""nem lejárt"",AND(RC[-1]>=1,RC[-1]<=30),""1-30"",AND(RC[-1]>=31,RC[-1]<=60),""31-60"",AND(RC[-1]>=61,RC[-1]<=90),""61-90"",AND(RC[-1]>=91,RC[-1]<=120),""90-120"",AND(RC[-1]>=121,RC[-1]<=180),""121-180"",AND(RC[-1]>=181,RC[-1]<=360),""181-360"",RC[-1]>361,""360 nap feletti tartozás"")"

ERP.Range("O2").Select
Selection.AutoFill Destination:=Range("O2:O1301")

However it keeps running on error on the last statement

Strange thing is if I run this code embedded on the second worksheet it runs perfect.

Could you please advise what could be the problem ?

Thanks Andras

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
András Czeitner
  • 61
  • 6
  • Please format your code as such using the button in the editor. Also provide the error message and the line where it occurs. – Luuklag Oct 08 '18 at 07:13
  • Avoid using `Activate`, `ActiveCell`, `Selection` and `.Select` and you don't have these issues. Read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and apply this technique on your code. If you then still have issues come back with your updated code. – Pᴇʜ Oct 08 '18 at 07:23

1 Answers1

0

IF and IFS 'short circuit' on true so shorten your formula to remove unnecessary criteria.

Write all of the formulas at once.

...

ERP.Activate
ERP.Range("O1").Value = "Korositás intervallum"

ERP.Range("O2:O1301").FormulaR1C1 = _
    "=IFS(RC[-1]<=0, ""nem lejárt"", RC[-1]<=30, ""1-30"", RC[-1]<=60, ""31-60"", RC[-1]<=90, ""61-90"", RC[-1]<=120, ""90-120"", RC[-1]<=180, ""121-180"", RC[-1]<=360, ""181-360"", RC[-1]>361, ""360 nap feletti tartozás"")"

Here's another option that shortens the formula even more.

ERP.Range("O2:O1301").Formula = _
    "=IFS(N2<=0, ""nem lejárt"", N2<=180, SUM(FLOOR(N2, 30), 1)&"-"&CEILING(N2, 30), N2<=360, ""181-360"", ""360 nap feletti tartozás"")"