vba matric

[hoareau]

Why this macro does not work


Sub test_mat_array()
Array_1 = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
test_2 = Application.WorksheetFunction.Sum((Array_1 >= 1) * (Array_1 <= 5))

Cells(1, 1) = test_2

End Sub



thank you

 

[Lewiy]

What are you trying to do?

 

[Andrew Poulsom]

Is this what you want?

Sub test_mat_array()
    With ActiveWorkbook
        .Names.Add Name:="ArrTemp", RefersToR1C1:="={1,2,3,4,5,6,7,8,9,10}"
        .ActiveSheet.Cells(1, 1).Value = Evaluate("=SUMPRODUCT(--(ArrTemp>=1),--(ArrTemp<=5))")
        .Names("ArrTemp").Delete
    End With
End Sub

 

[hoareau]

Thank you for your reply


To know the number of Value between two Values
Array_1 do not have reference on the excel sheet

what wants that's exactly, but it do not work.
the values of MyArray_1 are righteous man an example

Sub test_mat_array()
Array_1 = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
test_2 = Application.WorksheetFunction.Sum((Array_1 >= 1) * (Array_1 <= 5))
Cells(1, 1) = test_2

End Sub

 

[Andrew Poulsom]

Did you try the code I posted?

 

[hoareau]

It during what I want, can explain me line by line your macro

- - serves in what

thank you

 

[tusharm]

You have to understand the difference between Excel and VBA. While the two work closely, they are not the same or even interchangeable.

You are using an Excel capability (array formula evaluation) with a VBA entity (an array).

The example posted by Andrew uses a named formula, which is an Excel entity. Consequently, when used with Evaluate (a function with which VBA asks Excel to evaluate the function's argument) the entire analysis is done within Excel by Excel and the result is returned to VBA.

Andrew's example will work for the most part. It will run into problems if there is no active workbook or if the book is protected.

Why this macro does not work


Sub test_mat_array()
Array_1 = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
test_2 = Application.WorksheetFunction.Sum((Array_1 >= 1) * (Array_1 <= 5))

Cells(1, 1) = test_2

End Sub



thank you

 

[hoareau]

thank you

for code and explanation

 

[hoareau]

Why does nort work

With ActiveWorkbook

A = 1
B = 2
C = 3
d = 4
E = 5
F = 6
G = 7
H = 8
I = 9
j = 10

.Names.Add Name:="ArrTemp", RefersToR1C1:="={A,B,C,D,E,F,G,H,I,J}"
.ActiveSheet.Cells(1, 1).Value = Evaluate("=SUMPRODUCT(--(ArrTemp>=1),--(ArrTemp<=6))")
.Names("ArrTemp").Delete
End With

thank you