Solved: Pease help on CTRL-SHIFT ENTER on my VBA Formula

[genracela]

My formula is in B4 is:

{=SUM(IF(FREQUENCY(IF('Raw Data'!F$3:F$65000=D4,MATCH('Raw Data'!B$3:B$65000,'Raw Data'!B$3:B$65000,0)),ROW('Raw Data'!B$3:B$65000)-ROW('Raw Data'!B$3)+1),1))}

I used CTRL-SHIFT ENTER instead of just entering it, and it shows {}. If I don't use CTRL-SHIFT ENTER it will return a different answer so I just used it in my cell.

But my problem is, when I trnsferred it to my VBA code:
[VBA]Range("B4").Formula = "=SUM(IF(FREQUENCY(IF('Raw Data'!F$3:F$65000=D4,MATCH('Raw Data'!B$3:B$65000,'Raw Data'!B$3:B$65000,0)),ROW('Raw Data'!B$3:B$65000)-ROW('Raw Data'!B$3)+1),1))"[/VBA]

it returns a different answer, since the "{}" wasn't there.

I tried typing "{}" but my code just returns an error, how will I modify my VBA formula so it will return the correct answer?

Thanks!

[Blade Hunter]

Do you need the array formula in there or just the result? Array formulas are quite resource intensive and will be evaluated each time your sheet calculates. If it is being compiled by code anyway would it not be better to write the equivelant function in VBA code and populate the result to the cell or do you specifically need the formula in there?

[genracela]

Actually, not just in B4 but, I'm just trying to test it if it will return the right answer.

I only want the result.

If there's a way to change this to a VBA way the better, but this is the only way I know to write a code

I'll use the formula in the whole column B

The original VBA code that I'm doing is:
[vba]
Sub Calculation()

Dim lRow As Long

lRow = Range("A:A").Find(what:="*", LookIn:=xlValues, SearchOrder:=xlByRows, _
searchdirection:=xlPrevious).Row

Range("B4:B & lRow).Formula = "=SUM(IF(FREQUENCY(IF('Raw Data'!F$3:F$65000=D4,MATCH('Raw Data'!B$3:B$65000,'Raw Data'!B$3:B$65000,0)),ROW('Raw Data'!B$3:B$65000)-ROW('Raw Data'!B$3)+1),1))"
Range("C4:B" & lRow).Formula = "=SUM(IF(FREQUENCY(IF('Raw Data'!F$3:F$65000=D4,IF('Raw Data'!K$3:K$65000="N",MATCH('Raw Data'!B$3:B$65000,'Raw Data'!B$3:B$65000,0))),ROW('Raw Data'!B$3:B$65000)-ROW('Raw Data'!B$3)+1),1))"

End Sub
[/vba]

[mdmackillop]

[VBA]Range("B4").FormulaArray = [/VBA]

[Bob Phillips]

Do you need the array formula in there or just the result? Array formulas are quite resource intensive and will be evaluated each time your sheet calculates.

Excel is smarter than that, array formulae are the same as otherv formulae in that they are only recalculated if they are part of the calculation chain when something triggers the calculate event, otherwise they are not recalculated.

[Bob Phillips]

You also need to be careful with embedded quotes in a formula; I would also use a dynamic last row not hard coded 65000; and if you try to apply an array formula to a range in one hit it will be a block-array formula.

[vba]

Sub Calculation()
Const FORMULA_1 As String = _
"=SUM(If(FREQUENCY(If( 'Raw Data'!F$3:F$<row>=D4,MATCH('Raw Data'!B$3:B$<row>,'Raw Data'!B$3:B$<row>,0)),ROW('Raw Data'!B$3:B$<row>)-ROW('Raw Data'!B$3)+1),1))"
Const FORMULA_2 As String = _
"=SUM(IF(FREQUENCY(IF('Raw Data'!F$3:F$<row>=D4,IF('Raw Data'!K$3:K$<row>=""N"",MATCH('Raw Data'!B$3:B$<row>,'Raw Data'!B$3:B$<row>,0))),ROW('Raw Data'!B$3:B$<row>)-ROW('Raw Data'!B$3)+1),1))"
Dim lRow As Long

lRow = Range("A:A").Find(what:="*", LookIn:=xlValues, SearchOrder:=xlByRows, _
searchdirection:=xlPrevious).Row

Range("B4").FormulaArray = Replace(FORMULA_1, "<row>", lRow)
Range("C4").FormulaArray = Replace(FORMULA_2, "<row>", lRow)
Range("B4:C4").AutoFill Range("B4:C4").Resize(lRow - 3)

End Sub
[/vba]

[gengcas]

B4 calculates but did not return the correct answer, instead of "4" it returns "1", the same answer as when I just use ENTER instead of CTRL-SHIFT-ENTER.

C4 returns an error(#VALUE).

Then in the VBA editor this line was higlighted in yellow.

[vba]Range("B4:C4").AutoFill Range("B4:C4").Resize(lRow - 3)[/vba]

And when I put my cursor on the line it says

Range("B4:C4").Resize(lRow - 3) = <Application-Defined or Object-Defined Error>

[Bob Phillips]

I just tidied up the code that I saw, and I certainly don't get a compilation error, but without the workbook it is hard to make any suggestions. Can you post your workbook?

[gengcas]

Here's the attachment.

And Thank you for finding the time to help me, I appreciate it!

[Bob Phillips]

It is caused because you hav e no country names in column A, and you calculate the last row based on column A.

[Selpaqm]

Hi Guys,

Just I am stucked and lost mid of macro.

I have 2 different sheets and need to combine them. I have write a formula but it needs ctrl+shift+enter to run =INDEX(U2:U30,SMALL(IF($V$2=$F$2:$F$30,MATCH(ROW($F$2:$F$30),ROW($F$2:$F$30 )),""),ROWS(A$1:A1))
and I need to add each results of this formula together with &"", "& in a cell.

Can anyone help me?

thx

[Paul_Hossler]

Hi Guys,

Just I am stucked and lost mid of macro.

I have 2 different sheets and need to combine them. I have write a formula but it needs ctrl+shift+enter to run =INDEX(U2:U30,SMALL(IF($V$2=$F$2:$F$30,MATCH(ROW($F$2:$F$30),ROW($F$2:$F$30 )),""),ROWS(A$1:A1))
and I need to add each results of this formula together with &"", "& in a cell.

Can anyone help me?

thx

Welcome to the forum, but it's better to start your own thread instead of tagging on to an 8 year old one

Please try again, using the [Post New Thread] button

I'm closing this one