array formula in vba

  • Thread starter Thread starter maciej.grzywna
  • Start date Start date
M

maciej.grzywna

Hi!

I have a worksheet in which I have a following array formula entered
in Q1:Q1000

={IF(MAX(IF($B$1:$B$1000=B2;$P$2:$P$1000))<30;0;1)}

I would like to write a macro that would enter such formula into
Q1:Q1000, the problem that I can't solve is the number of rows that is
changing, sometimes I have 1000 rows, sometimes more. How to adjust
the formula under vba to consider the number of rows?

TIA
Maciek
 
Your formula looks kind of weird. You started in B1 in one portion and P2 in
another. And you wanted to put the formula in Q1:Q###.

I'm gonna guess that you really meant to start in Row 2 for all those ranges.

Option Explicit
Sub testme()
Dim LastRow As Long
Dim myFormula As String
Dim wks As Worksheet

myFormula = "=IF(MAX(IF($B$2:$B$###=B2,$P$2:$P$###))<30,0,1)"

Set wks = Worksheets("sheet1")

With wks
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
myFormula = Replace(myFormula, "###", LastRow)
.Range("Q2").FormulaArray = myFormula
.Range("Q2:Q" & LastRow).FillDown
End With

End Sub

If I'm wrong, maybe this will help a little anyway.
 
Hi Dave,
You're right, it should be $B$2:$B$1000. Anyway, your solution worked great,
thank you very much.

Maciek
 
Dave,
can you tell me why do I receive an error when I want to use following
normal formula (not array formula) ?

Sub test()
Dim LastRow As Long
Dim myFormula As String
Dim wks As Worksheet

myFormula = "=IF(SUMIF($B$2:$B$###,B2,$L$2:$L$###))<0,1,0)"

Set wks = Worksheets("sheet1")

With ActiveSheet
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
myFormula = Replace(myFormula, "###", LastRow)
.Range("S2").Formula = myFormula
.Range("S2:S" & LastRow).FillDown
End With

End Sub
 
You have an extra ")" in your formula:

myFormula = "=IF(SUMIF($B$2:$B$###,B2,$L$2:$L$###)<0,1,0)"

I like to do this to help debug my formulas:
myFormula = "$=IF(SUMIF($B$2:$B$###,B2,$L$2:$L$###))<0,1,0)"
This means that the formula is really text (that leading $).

Then I'll go back to excel and remove that leading $.
And excel will either accept it or yell at me -- possibly with a hint at what's
wrong. In this case, the cursor was on that extra ")"

As an aside, you could use a formula that returns true or false, then use --
(two minus signs) to convert true to +1 and False to 0.

myFormula = "=--(SUMIF($B$2:$B$13,B2,$L$2:$L$13)<0)"

The first minus changes True to -1 and the second changes -1 to +1.
 
I corrected the formula as you told, but when I try to execute the macro I
get "Run-time error '1004': Application-defined or object-defined error" and
"ActiveCell.FormulaR1C1 = myFormula" turns yellow.

Sub ujemne()
Dim LastRow As Long
Dim myFormula As String

myFormula = "=--(SUMIF($B$2:$B$###,B2,$L$2:$L$###)<0"


With ActiveSheet
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
myFormula = Replace(myFormula, "###", LastRow)
Range("S2").Select
ActiveCell.FormulaR1C1 = myFormula
Range("S2:S" & LastRow).FillDown
End With


End Sub
 
Almost!

You missed the final ")"

When you tried the debugging technique, did it help?


Maciej said:
I corrected the formula as you told, but when I try to execute the macro I
get "Run-time error '1004': Application-defined or object-defined error" and
"ActiveCell.FormulaR1C1 = myFormula" turns yellow.

Sub ujemne()
Dim LastRow As Long
Dim myFormula As String

myFormula = "=--(SUMIF($B$2:$B$###,B2,$L$2:$L$###)<0"

With ActiveSheet
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
myFormula = Replace(myFormula, "###", LastRow)
Range("S2").Select
ActiveCell.FormulaR1C1 = myFormula
Range("S2:S" & LastRow).FillDown
End With

End Sub
Click to expand...
 
ps. When you try that debugging technique, you'll have to change the list
separator (comma ",") to what you use (a semicolon ";").
 
Hi Dave,

I tried your debugging technique, I added "$", excel entered myFormula into
the cell and when I deleted "$" it worked, i.e. formula is OK (of course
after adding missing ")" ), but when I delete "$" from vba and try to run
macro I get: Run-time error '1004' Application-defined or object-defined
error. When I hit Debug button Visual Basic Editor opens and
'ActiveCell.FormulaR1C1 = myFormula' from my macro is marked with yellow, I
don't know what's wrong with this macro

Sub ujemne()
Dim LastRow As Long
Dim myFormula As String

myFormula = "=--(SUMIF($B$2:$B$9493;B2;$L$2:$L$9493)<0)"


With ActiveSheet
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
myFormula = Replace(myFormula, "###", LastRow)
Range("S2").Select
ActiveCell.FormulaR1C1 = myFormula
Range("S2:S" & LastRow).FillDown
End With

ActiveSheet.Calculate
End Sub
 
First, your formula isn't in R1C1 reference style--it's in A1 style. So you
don't want to use .formulaR1C1.

Second, you change the "master" formula. You hardcoded numbers in the range. I
used ### to represent the lastrow. The replace() won't find anything to do if
you change that formula

Third, you don't need to select a cell to work with it.

Fourth, is there a reason you added the .calculate statement. It makes sense if
you're not in automatic mode.

Sub ujemne()
Dim LastRow As Long
Dim myFormula As String

myFormula = "=--(SUMIF($B$2:$B$###;B2;$L$2:$L$###)<0)"

With ActiveSheet
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
myFormula = Replace(myFormula, "###", LastRow)
.Range("S2").Formula = myFormula
.Range("S2:S" & LastRow).FillDown
End With

End Sub

Maciej said:
Hi Dave,

I tried your debugging technique, I added "$", excel entered myFormula into
the cell and when I deleted "$" it worked, i.e. formula is OK (of course
after adding missing ")" ), but when I delete "$" from vba and try to run
macro I get: Run-time error '1004' Application-defined or object-defined
error. When I hit Debug button Visual Basic Editor opens and
'ActiveCell.FormulaR1C1 = myFormula' from my macro is marked with yellow, I
don't know what's wrong with this macro

Sub ujemne()
Dim LastRow As Long
Dim myFormula As String

myFormula = "=--(SUMIF($B$2:$B$9493;B2;$L$2:$L$9493)<0)"

With ActiveSheet
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
myFormula = Replace(myFormula, "###", LastRow)
Range("S2").Select
ActiveCell.FormulaR1C1 = myFormula
Range("S2:S" & LastRow).FillDown
End With

ActiveSheet.Calculate
End Sub
Click to expand...
 
OK, my mistake with hardcoding the numbers in the range. I need calculate
because I'm in manual mode. And your macro - it isn't working. The formula
is ok when using your debugging technique, but when I delete "$" I get the
same error message and I'am still lost in the forest :(
 
I didn't notice that you changed the formula.

Excel's VBA is USA centric. It likes the common USA list separator--comma, not
semicolon. (That was what my earlier not was trying to warn you.)

Option Explicit

Sub ujemne()
Dim LastRow As Long
Dim myFormula As String

myFormula = "=--(SUMIF($B$2:$B$###,B2,$L$2:$L$###)<0)"

With ActiveSheet
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
myFormula = Replace(myFormula, "###", LastRow)
.Range("S2").Formula = myFormula
.Range("S2:S" & LastRow).FillDown
End With

End Sub



Maciej said:
OK, my mistake with hardcoding the numbers in the range. I need calculate
because I'm in manual mode. And your macro - it isn't working. The formula
is ok when using your debugging technique, but when I delete "$" I get the
same error message and I'am still lost in the forest :(
Click to expand...
 
(That was what my earlier not was trying to warn you.)
should have been:
(That was what my earlier notE was trying to warn you.)
 
Dave,

everything works just fine! Thanks for your time and for answering my
questions.

Maciek
 
Back
Top