vba matric

hoareau

Board Regular
Joined
Apr 11, 2007
Messages
51
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Is this what you want?

Code:
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
 
Upvote 0
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
 
Upvote 0
It during what I want, can explain me line by line your macro

- - serves in what

thank you
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top