Multidimensional Array - Variant - Loop Through Sets of Arrays

[dj44]

Folks good day,

Well I figured I rather as well try and ask for help and understand what this array is all about.


For each array

I would like to replace term 1 with term2

But I really don't know how to explain this

And I can't even search to explain what this is

But I know this exists

    Sub Array_Multiple_Terms()

    Dim oRng As Word.Range
    
    Dim oProduct As Variant    ' declare some variant
    
    
    Dim oReplace As Variant
    Dim i As Long, j As Long
    

   ' Each product has its properties - Loop through each array 1 at a time
   
   
    oProduct1 = Array("Apple", "Green", "20")
    oProduct2 = Array("Banana", "Yellow", "30")
    oProduct3 = Array("Cherry", "Red", "50")
    
    
        '--------------------------
        For i = 0 To UBound(oProduct)
            Set oRng = ActiveDocument.Range
            j = 0
            With oRng.Find
            
            
                Do While .Execute(FindText:=oProduct(i), MatchWholeWord:=True)    x wrong
                
           
                    oRng.Text = oProduct(i)(2)       x wrong
                    
                    oRng.Collapse 0
                Loop
            End With
        Next i
        Set oRng = Nothing
        
   
    End Sub

The main code will look for

Apple replace > green
Banana replace > Yellow

and loop through more products arrays


If anyone can shed some light on what this is

that would be awesome and thank you for your help

[mdmackillop]

Changed
Get an Apple, a Banana and a Cherry.
to
Get an Green, a Yellow and a Red.

Sub Array_Multiple_Terms()     
    Dim oRng As Word.Range
    Dim oProduct As Variant ' declare some variant
     
     ' Each product has its properties - Loop through each array 1 at a time
    oProduct1 = Array("Apple", "Green", "20")
    oProduct2 = Array("Banana", "Yellow", "30")
    oProduct3 = Array("Cherry", "Red", "50")
    oProduct = Array(oProduct1, oProduct2, oProduct3)
    
    For Each oP In oProduct
        Set oRng = ActiveDocument.Range
        With oRng.Find
            Do While .Execute(FindText:=oP(0), MatchWholeWord:=True)
                oRng.Text = oP(1)
                oRng.Collapse 0
            Loop
        End With
    Next oP
    Set oRng = Nothing
End Sub

[mdmackillop]

Alternative, nearer to your proposed method

oProduct = Array(oProduct1, oProduct2, oProduct3) 
For j = LBound(oProduct) To UBound(oProduct)
        Set oRng = ActiveDocument.Range
        With oRng.Find
            Do While .Execute(oProduct(j)(0), MatchWholeWord:=True)
                oRng.Text = oProduct(j)(1)
                oRng.Collapse 0
            Loop
        End With
    Next j

[dj44]

Hello M,

thank you for the 2 really good examples - simple enough for even me to understand now.

I make a master array out of my baby arrays

So it's an array of arrays.

We've got array inception going on ill keep it to 5 arrays maximum

But this does help me to lay out all the arrays in a logical order and then do some processing


I usually have difficulties because the arrays go off the screen where I can't see them

and the screen bounces and its really hard to make these long arrays and format them

And I usually delete terms and then it messes up the other array.

Then a type mismatch happens.

so I wanted to keep the array of items short and this will help me

well thank you for the great help today.

and a very splendid and good evening to you and all

[macropod]

I usually delete terms and then it messes up the other array.

Then a type mismatch happens.

The approaches described by mdmackillop won't help with that!

[dj44]

Well im very good at causing mischief for myself,

i set it up nicely promising not to touch it but lo and behold somehow and i swear the next time i come and check the array
i added an item and didnt update its partner array.


so i have to count the items 1 by 1 and then i have to set it up all over again,

so i do try to keep myself away from arrays, but even so vba just isnt the same without its king spice - the array

so i have to submit to its mighty power, or as usual i have code thats 10 pages long alas... that becomes the most unelegant looking montrsosity

[macropod]

You could, of course, put all your data in an Excel workbook, for example, and retrieve it from there. Any properly-implemented process that gets all the populated rows (e.g. via ADODB) will get any empty cells as well. It's also very easy to see whether there are gaps in your data there.