Looping through a range for items in one list and using items from another list to update a range of cells with matching criteria

[kelly mort]

Hi,

I have a variable MyItems as:

MyItems = ("Item 1", "Item 2", ....., "Item 11")

I also have 11 variables like:
Var1, Var2, ....., Var11


What I want to do is:
Look through column C for "Item 1" and when found, all records with Item 1 should have their values in column G, set to the value stored in Var1.
Then I repeat same for Item 2 to match Var2, and so on.


In my mind, I was thinking of loading those variables too to a list or an array.

MyVar = (Var1, Var2, ....., Var11)

But I don't know how to load it.

If what I am thinking is possible, can someone help me out?

Thanks in advance

 

[JackDanIce]

Can you post your full code and an example of the sheet pls?

What about using a filter on columns C:G and looping MyItems through that?

 

[kelly mort]

Can you post your full code and an example of the sheet pls?

What about using a filter on columns C:G and looping MyItems through that?

@JackDanIce,

I don't have the code yet. I want to know if it's possible before I get the code (possibly, I may not write it myself since I have no idea how to handle the logics. Lol).

And about using the filter, I don't know how to do that so I will be glad if you can show me how to do it.

Once it works fine, I am cool with it.

 

[kelly mort]

Hello again @JackDanIce

If I am able to load all my valuables to an array as discussed before, I should be able to achieve what I want to achieve.

In that case a pseudo loop like this could help:


For i = 0 To UBound(MyItems)
For Each cel In MyItemsRng
If cel = MyItems(i) Then
cel.Offset(, 4) = MyVar(i)
End if

Next cel
Next i

 

[JackDanIce]

If you're not going to write the code, why are you asking about a hypothetical situation?

A reader can't see your PC so:
Where is the list of items to load into an array?
What does this output sheet with column C look like?
Does a reader have the same understanding as you do, of your problem, that you are then asking for help?

In short, yes it is possible but I wouldn't use a nested loop as it's too inefficient and slow.
I'd probably use a filter but even that is an uncertainty because I don't know what the data is or data size is.
Maybe I'd use a dictionary with a single array to avoid a nested loop or more likely suggest a PowerQuery solution to reduce VBA need entirely

 

[johnnyL]

Something like this maybe:

Book1
	A	B	C	D	E	F	G
1
2			Item 1				Var1 value
3			Item 5				Var5 value
4			Item 3				Var3 value
5			Item 11				Var11 value
6			Item 2				Var2 value
7			Item 4				Var4 value
8			Item 6				Var6 value
9			Item 7				Var7 value
10			Item 1				Var1 value
11			Item 8				Var8 value
12			Item 10				Var10 value
13			Item 9				Var9 value
14			Item 2				Var2 value
15			Item 11				Var11 value
16			Item 7				Var7 value
17			Item 4				Var4 value
18			Item 9				Var9 value
19			Item 1				Var1 value
20			Item 5				Var5 value
21			Item 10				Var10 value
22			Item 6				Var6 value
23			Item 3				Var3 value
24			Item 8				Var8 value
Sheet1

Cell Formulas
Range	Formula
G2:G24	G2	=IFERROR(INDEX(Sheet2!B:B,MATCH(Sheet1!C2,Sheet2!A:A,0)),"")

 

[kelly mort]

@JackDanIce,

I wrote the code afterwards like this:

MyVar = Array(Var1, Var2, Var3, Var4, Var5)
MyItems = Array("Item 1", "Item 2", "Item 3", "Item 4", "Item 5")

For i = 0 To UBound(MyItems)
      For Each cel In MyItemsRng
           If cel = MyItems(i) Then
               cel.Offset(, 4) = MyVar(i)
           End if
      Next cel
Next i

It's working fine for now so I will be using it atm until I find a better alternative.

And thanks @johnnyL for the table.