Array Doubt

[irisqueiroz]

Hi!

I'm trying to right a code that needs a dynamic array.
Basically, I'd like to look into all found values (method find) in a current region and store them address in a array. For example, if I find 4 values (then j=4, in my code) in the region that im looking for, then i'd like my array to be:
endereço(1) = A1
endereço(2) = A2
endereço(3) = A3
endereço(4) = A4

My array varies in function of j.
how can I write it?

Sub tratar()
 
Dim endereço() As Range
 
uLinha = Range("B1048576").End(xlUp).Row 'última linha
 
i = 24
 
Do Until i = uLinha
 
    If trat.Range("B" & i) = "NOTA" Then
        trat.Range("B" & i).Select
       
        'determinando quantos "tipos" tem no current region
        Selection.CurrentRegion.Select
        Selection.Find(What:="tipo", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
        lprim = ActiveCell.Row 'linha da 1a celula ativa
 
        Do
            j = j + 1 'quantidade de "tipo"
            Selection.FindNext(After:=ActiveCell).Activate
            endereço(j) = ActiveCell.address        
        Loop While ActiveCell.Row > lprim

 

[shrivallabha]

What is the purpose of building up an array? Could you let us know the objective? Maybe there's easy way round.

 

[irisqueiroz]

the purpose ir to take some informations that are below each cell that contains the word "tipos".

 

[NARAYANK991]

Hi Irisqueiroz ,

To use a dynamic array , all you need to do is two things :

When you declare it , the first time do this :

Dim array_name() as .... Variant , Range , String , Long , Integer ,...

Later on , before you start using it , write this statement :

ReDim array_name(1 to upper_limit)


where upper_limit has a defined value.

Of course , there is more to it , but this much is sufficient for you to use an array dynamically.

Narayan

 

[shrivallabha]

If you could upload a sample file then it will be easier to work out something.

And in your case, you really should not need ARRAY you can use UNION method to group all cells together. Something like below [untested]

Dim r as range, rU as range, rUsed range
Set rUsed = Range("B1:B100000")
For each r in rUsed
    If instr(r.value,"tipo") > 0 then
      If rU is nothing then
          set rU = r
      else
          set rU = Union(rU,r)
      end if
    End if
Next r

 

[SirJB7]

@sandip
Hi!
Would you please start a new topic instead of writing on another user's one? It might be seen as hijacking. More indeed when it's no related at all with the main subject. If needed you could add a reference in your new one.
Perhaps you'd want to read the red sticky posts at this forums main page so as to know the guidelines that will lead to know how this community operates (introducing yourself, posting files, netiquette rules, and so on).
Regards!
PS: Please don't answer here at this thread.