Formula/VBA - Application defined or object defined error

Question

I'm trying to create a vba code to write a formula in determined cell on my sheet, but it doesn't work. What's wrong ? 'Cause, i really don't see what am i doing wrong in this code.

Range("BC" & ActiveCell.Row).Formula = "=IF($BB" & ActiveCell.Row & "=" & """" & "REVIEW" & """" & ";IF(ROW($BB" & ActiveCell.Row & ")<MAX(IF($BB:$BB=" & """" & "OK" & """" & ";$A:$A));IF(TODAY()-$AY" & ActiveCell.Row & ">=3;" & """" & "DROP" & """" & ";" & """" & "REVIEW" & """" & ");" & """" & "REVIEW" & """" & ");" & """" & """" & ")"

Are you sure you want $BB2 and then a number? if the row is 10 you'll get BB210 — Jchang43
– Jchang43, Commented Oct 29, 2018 at 20:34
Pull that string into a local variable, Debug.Print the string, grab it from the immediate pane (Ctrl+G), paste it into an empty cell. Excel will tell you what's wrong with it, if it doesn't jump at you right away from the debug output. — Mathieu Guindon
– Mathieu Guindon, Commented Oct 29, 2018 at 20:36
@MathieuGuindon the variable are debuging this: =IF($BB2="REVIEW";IF(ROW($BB2)<MAX(IF($BB:$BB="OK";$A:$A));IF(TODAY()-$AY2>=3;"DROP";"REVIEW");"REVIEW");"") — L.Th
– L.Th, Commented Oct 29, 2018 at 20:43
change the ; to , or use .FormulaLocal instead of .Formula. — user4039065
– user4039065, Commented Oct 29, 2018 at 21:13
... and the MAX(IF($BB:$BB="OK";$A:$A)) part tells me that should be .FormulaArray, not .Formula. — user4039065
– user4039065, Commented Oct 29, 2018 at 21:15

score 3 · Accepted Answer · 2018-10-30 06:44:41Z

3

Your computer's use of a ; as the regional list separator is fouling you up. VBA is very EN-US-centric so the Range.Formula and Range.FormulaR1C1 expect a comma (,) as the function's argument list separator.

Range("BC" & ActiveCell.Row).Formula = "=IF($BB" & ActiveCell.Row & "=" & """" & "REVIEW" & """" & ", IF(ROW($BB" & ActiveCell.Row & ")<MAX(IF($BB:$BB=" & """" & "OK" & """" & ", $A:$A)), IF(TODAY()-$AY" & ActiveCell.Row & ">=3, " & """" & "DROP" & """" & ", " & """" & "REVIEW" & """" & "), " & """" & "REVIEW" & """" & "), " & """" & """" & ")"

Alternately, Range.FormulaLocal property or Range.FormulaR1C1Local property can be used with your own semi-colon as the list separator.

Range("BC" & ActiveCell.Row).FormulaLocal = "=IF($BB" & ActiveCell.Row & "=" & """" & "REVIEW" & """" & ";IF(ROW($BB" & ActiveCell.Row & ")<MAX(IF($BB:$BB=" & """" & "OK" & """" & ";$A:$A));IF(TODAY()-$AY" & ActiveCell.Row & ">=3;" & """" & "DROP" & """" & ";" & """" & "REVIEW" & """" & ");" & """" & "REVIEW" & """" & ");" & """" & """" & ")"

Your doubling up double quotes within a quoted string is a bit verbose and xlR1C1 notation would save some steps. TEXT(,) is the same as "" in a formula; each produces the same zero-length string.

Range("BC" & ActiveCell.Row).Formula = "=IF($BB" & ActiveCell.Row & "=""REVIEW"", IF(ROW($BB" & ActiveCell.Row & ")<MAX(IF($BB:$BB=""OK"", $A:$A)), IF(TODAY()-$AY" & ActiveCell.Row & ">=3, ""DROP"", ""REVIEW""), ""REVIEW""), TEXT(,))"
'.FormulaR1C1
Range("BC" & ActiveCell.Row).FormulaR1C1 = "=IF(RC54=""REVIEW"", IF(ROW(R:R)<MAX(IF(C54:C54=""OK"", C1:C1)), IF(TODAY()-RC51>=3, ""DROP"", ""REVIEW""), ""REVIEW""), TEXT(,))"

The xlA1 and xlR1C1 formula rewrites above each produce the following when ActiveCell is on the second row.

=IF($BB2="REVIEW", IF(ROW($BB2)<MAX(IF($BB:$BB="OK", $A:$A)), IF(TODAY()-$AY2>=3, "DROP", "REVIEW"), "REVIEW"), TEXT(,))

As I mentioned in comments above, that formula would seem to be an array formula. In that case, use the .Formula comma based rewrite but change the Range.Formula property to Range.FormulaArray.

edited Oct 30, 2018 at 6:44

answered Oct 29, 2018 at 21:48

user4039065

Sign up to request clarification or add additional context in comments.

4 Comments

Marcucciboy2 Over a year ago

Gratz on 100k by the way! :D (edit: whoops, that’s premature because so likes to round rep, but still!)

Imran Malek Over a year ago

@Jeeped ++++ there you are , you deserve it :)

Imran Malek Over a year ago

@Jeeped Thanks for giving so much to the community , you're mug will be on the way soon :D

L.Th Over a year ago

@Jeeped the Range.FormulaLocal works to me ! Thank you very much !

Collectives™ on Stack Overflow

Formula/VBA - Application defined or object defined error

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related