2

I'm kinda stuck with this task. I have a macro that retrieve two ranges of data to process from two different workbooks and stuff them into two Variant. The gathering is successful as I can debug and inspect them in Local window, one being data1(variant 1 to 79) and other data2(variant 1 to 10). This is how I get data1

 With wbExterno.Sheets(1)
data1 = .Range(tmpStr + ":" & ToolboxMod.Number2Char(lastCol) & lastRow).Value
End With

Now I want to copy relevant elements from data1 to a new array, name it Dim newData as Variant. I've already checked in SO about this and this is what I got

Dim filterCount As Integer
    counter = 0
    filterCount = 1    
' Para cada elemento en el array...
        For i = 1 To UBound(data1)

            'Comparar el campo fecha...
            tmpTest = data1(i, 1)

            ' ...con la fecha del ejercicio
            If (comparacionActual.FechaEjercicio = tmpTest) Then
                'MsgBox "iguales!"

                'se crea un array filtrado con los elementos pertinentes
                filter1(filterCount) = Application.WorksheetFunction.Index(data1, 0, i)

                PlusOne filterCount 'this is a custom function that increments in 1
            End If
        Next

        ' se informa el resultado del filtrado
        MsgBox "Copied: " & filterCount & " rows."

It raises a Error 1004 unable to get Index property from worksheetfunction. What am I doing wrong here? Should I filter the input into data1 instead? Easier that way? Faster?

EDIT: I've tried the method in a worksheet with the same data (no VBA) and it only worked with the translated method name ( =Index() in spanish is =Indice() ). Other than that, it worked. But, tried setting data1 as range and variant and... it didn't work. Also, I checked with a breakpoint and data1 is a Variant/Variant(78) and each element (data1(i) ) is a Variant (1 To 8) containing each row

EDIT 2: After a test suggested in the answers, I tried a more old-school approach:

With wbInterno.Sheets(1)
    data1 = .Range(tmpStr + ":" & ToolboxMod.Number2Char(lastCol) & lastRow).Value
    filter1 = wbMe.Worksheets.Add.Range(tmpStr + ":" & ToolboxMod.Number2Char(lastCol) & lastRow).Value
End With

'... some other stuff

' Field by field
For j = LBound(data1, 2) To UBound(data1, 2)
    'MsgBox "check" & data1(i, j)
    filter1(filterCount, j) = data1(i, j)
Next

And it actually sets field by field, the rows I need in the new array. I'll leave the question unanswered; perhaps we can find a better way.

Solution The code I use in the end is this one:

     ' Define array with a range. Initialize destination array with the same size.
     data1 = .Range(tmpStr + ":" & ToolboxMod.Number2Char(lastCol) & lastRow).Value
     filter1 = wbMe.Sheets("tmp").Range(tmpStr + ":" & ToolboxMod.Number2Char(lastCol) & lastRow).Value

... some more code not relevant to this and then

     Dim tmpTest As Variant
        Dim filterCount As Integer
        filterCount = 1
        ' integer used for presentation only 
        conteoRegistros = 0        
' for each element in array...
        For i = 1 To UBound(data1)
            'Compare a certain field...
            tmpTest = data1(i, 1)
            ' ...with some other variable. If so...
            If (comparacionActual.FechaEjercicio = tmpTest) Then
                '...copy column by column into new
                For j = LBound(data1, 2) To UBound(data1, 2)
                    'MsgBox "check" & data1(i, j)
                    filter1(filterCount, j) = data1(i, j)
                Next

                PlusOne filterCount
                PlusOne conteoRegistros
            End If
        Next
Community
  • 1
  • 1
Alfabravo
  • 7,493
  • 6
  • 46
  • 82
  • So what is your variable filter1 dimensioned as? – Jon49 Dec 16 '11 at 20:17
  • I setted filter1 as Variant(). Should I give it dimension? Like setting a range just as large but blank? – Alfabravo Dec 16 '11 at 20:19
  • Surely the first argument for index in the above should be an array (range), but data1 is a value from your definition. See http://msdn.microsoft.com/en-us/library/ff197581.aspx – Fionnuala Dec 16 '11 at 20:37
  • If I set data1 as *.Range(tmpStr + ":" & ...)*, without the value, it still raises the same error. – Alfabravo Dec 16 '11 at 20:43
  • Set a breakpoint and examine what you are passing to the index function, then test it in the worksheet to see what the problem is. – Fionnuala Dec 16 '11 at 20:58

2 Answers2

1

OK, I had time to look at it. If your Index function doesn't have the correct parameters then it will throw the error 1004. Let's say if your range is a single cell, then when you look for a value in column 2, it won't come up with anything (i.e., error 1004). But column 1 row 1 will return a correct result.

So in your code make sure that the variable i is not being exceeded. So this is what is wrong in your code:

For i = 1 To UBound(data1)

Should be

For i = 1 To UBound(data1, 2)

So it wont exceed the number of columns in your array.

Jon49
  • 4,444
  • 4
  • 36
  • 73
  • Great! I'll check this out in a while and I'll let you know :) – Alfabravo Dec 17 '11 at 15:00
  • This gets the first row and then goes column by column, right? What I want to do is to copy some rows to a new Variant array, which I only can do with the code in the second edit. Thanks – Alfabravo Dec 19 '11 at 14:36
  • The last bit of code that I present is for illustrative purposes only, it is not meant for you to copy and use. What code was meant for you to copy is the code following the "should be" words. That is the problem, you are looping over the rows instead of the columns of your array. This should make the code work since you'll be entering valid data for your `index` function. Currently you are enter invalid data. I'll delete the illustrative code so as not to confuse you. – Jon49 Dec 19 '11 at 14:58
  • Indeed, that's what I understood. :) As you see, in the last update I did to the question (last friday), I set the code to run across the array just as you said. Thank you – Alfabravo Dec 19 '11 at 15:07
  • Then I have correctly answered your original question. Please mark it as such. – Jon49 Dec 19 '11 at 15:10
  • Hmmm, I found it by myself but... well, it's Christmas so here it goes. Thanks for your time and help. :) – Alfabravo Dec 19 '11 at 15:37
0

As Remou pointed out, the first argument should be a range.

However, youll need to slightly modify your code beyond just removing the .value.

data1 will need to be dim'd as a Range and assigned with the Set keyword like so:

Dim data1 as Range
Set data1 = wbExterno.Sheets(1).Range(tmpStr + ":" & ToolboxMod.Number2Char(lastCol) & lastRow)
Justin Self
  • 6,137
  • 3
  • 33
  • 48
  • I had to create also new variable ***Dim test as variant*** in order to perform *For i = 1 To UBound(test)* and run thru the array. In the end, it raises the same error :( – Alfabravo Dec 16 '11 at 22:38