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.:grinhalo:

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.:doh:

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

:beerchug:

[macropod]

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

Then a type mismatch happens.:doh:
The approaches described by mdmackillop won't help with that!

[dj44]

Well im very good at causing mischief for myself, :grinhalo:

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 :doh:

[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.