How to define an Array in VB excel with a range?

Question

How to define an Array in VB excel with a range?

Navigation

#1 by (0 votes)

1

Good morning community.

It turns out that I am looking for a way to filter, that allows me to select several references to filter, that is, by means of a macro I indicate to my Excel file that I look for a client by reference number.

Example: Client: 130, this gives me all the information of that client, from there I make another macro that helps me to filter the result obtained based on the first column "A2: A5000", for this researching and observing the code that I record Excel, I see that it uses an Array, which I would like to see if it can be defined and that it obtains the values of a specific range of cells.

Here is an example of the code:

Dim fase1(15, 1) As Variant

fase1(15, 1) = Sheets("BD").Range("A2:A15").Value
I = fase1(15, 1)

Sheets("SAAIO_FACTUR").Select
ActiveSheet.ListObjects("SAAIO_FACTUR").Range.AutoFilter Field:=1, Criteria1 _
:=I

However, the result thrown at me is only a filter in cell 15 of Excel.

I would like to be able to generate this arrangement with information from certain cells so that when my file with the data changes, the result of the same cells will continue to be filtered.

Thank you in advance for allowing yourself to read this topic and Thank you for supporting me, any intention counts a lot, I'm still on the path of learning.

macros excel vb

asked by Jmartinez 19.06.2018 в 00:26

source

1 answer

MySQL and ANDROID, JDBC Connector, How to perform a correct HANDSHAKE? @ Html.Captcha does not work correctly

score 0 · Accepted Answer

Ready I found the solution with the following code, thanks to this I can already filter automatically with the data that the Excel document gives me.

Dim rango As Range, celda As Range
Dim list() As String, lngCount As Long
Set rango = Sheets("BD").Range("A2:A100")

lngCount = 0
For Each celda In rango
    ReDim Preserve list(lngCount)
    list(lngCount) = celda.Text
    lngCount = lngCount + 1
Next

Sheets("SAAIO_FACPAR").Select
    ActiveSheet.ListObjects("SAAIO_FACPAR").Range.AutoFilter Field:=1, Criteria1:=list, Operator:=xlFilterValues