array formula in vba

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
 
D

Dave Peterson

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.
 
M

Maciej Grzywna

Hi Dave,
You're right, it should be $B$2:$B$1000. Anyway, your solution worked great,
thank you very much.

Maciek
 
M

Maciej Grzywna

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
 
D

Dave Peterson

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.
 
M

Maciej Grzywna

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
 
D

Dave Peterson

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...
 
D

Dave Peterson

ps. When you try that debugging technique, you'll have to change the list
separator (comma ",") to what you use (a semicolon ";").
 
M

Maciej Grzywna

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
 
D

Dave Peterson

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...
 
M

Maciej Grzywna

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 :(
 
D

Dave Peterson

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...
 
D

Dave Peterson

(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.)
 
M

Maciej Grzywna

Dave,

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

Maciek
 
Top