VBA - working with array [SOLVED]

[irisqueiroz]

Hi!


I'd like to know how can I can write a code that for each "i", I have a value in the variable linha(i). For example, in the column 2 (ie, i=2), the first filled row (ret.Range(Cells(253, i)).End(xlUp).Row) is 10, so linha(2) = 10 ... column 3, first filled row is 5, then linha(3) = 5 ... and so on, till i = nAtivos + 1.

Next I want to compare which value in array "linha" is the smallest. Could anyone help me?


Thats, what I thought... but didn't work out!


For i = 2 To nAtivos + 1

linha(i) = ret.Range(Cells(253, i)).End(xlUp).Row

Next


Thanks!

 

[Colin Legg]

What does "didn't work out" mean?


Assuming

ret is the worksheet's codename, shouldn't that be this?

[pre]For i = 2 To nAtivos + 1
linha(i) = ret.Cells(253, i).End(xlUp).Row
Next

Debug.Print Application.Min(linha)

[/pre]
If that doesn't work then please give full details (eg. wrong value, error message) and show us the complete code including variable declarations so we can help you further.

 

[Luke M]

This bit

linha(i) = ret.Range(Cells(253, i)).End(xlUp).Row

has an error in it. The first [code]Range object is being called as a child of [code]ret, but the [code]Cells

object has no defined parent. Thus, it will default to the active sheet. If the active sheet is not ret[/code], an error will be created. I think the line should be:

linha(i) = ret.Cells(253, i).End(xlUp).Row[/code]

That said, if you only need the smallest value, rather than loading an array, we can check as we go along like so:

[pre]Dim sValue As Long
sValue = 253
For i = 2 To 8 'nAtivos + 1
sValue = WorksheetFunction.Min(ret.Cells(253, i).End(xlUp).Row, sValue)
Next
MsgBox "Smallest row: " & sValue[/code][/pre]

 

[irisqueiroz]

I wrote this:


Dim linha() As Integer


Set ret = Sheets("Retornos")


nAtivos = 6 '<<<<<<<<<<<<<<<<<<<<<<<<<<


For i = 2 To nAtivos + 1

linha(i) = ret.Cells(253, i).End(xlUp).Row

Next


but it's still telling me that there is an error when i run the linha(i)=... line!

It´s "run time error 9 out of range"

 

[Colin Legg]

linha() is a dynamic array in your code, so you need to [code]ReDim

it. If I include your variable declarations for you:

[pre]Sub foo()

Dim linha() As Long
Dim nAtivos As Long
Dim i As Long
Dim ret As Worksheet

Set ret = Sheets("Retornos&#34

nAtivos = 6 '<<<<<<<<<<<<<<<<<<<<<<<<<<

ReDim linha(2 To nAtivos + 1)

For i = LBound(linha, 1) To UBound(linha, 1)
linha(i) = ret.Cells(253, i).End(xlUp).Row
Next

Debug.Print Application.Min(linha)

End Sub[/code][/pre]

 

[irisqueiroz]

It worked! Thanks!