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

Well-known Member
Joined
Apr 10, 2017
Messages
2,020
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,890
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 10, 2017
Messages
2,020
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Apr 10, 2017
Messages
2,020
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Feb 3, 2010
Messages
9,890
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Nov 7, 2011
Messages
1,799
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Something like this maybe:

Book1
ABCDEFG
1
2Item 1Var1 value
3Item 5Var5 value
4Item 3Var3 value
5Item 11Var11 value
6Item 2Var2 value
7Item 4Var4 value
8Item 6Var6 value
9Item 7Var7 value
10Item 1Var1 value
11Item 8Var8 value
12Item 10Var10 value
13Item 9Var9 value
14Item 2Var2 value
15Item 11Var11 value
16Item 7Var7 value
17Item 4Var4 value
18Item 9Var9 value
19Item 1Var1 value
20Item 5Var5 value
21Item 10Var10 value
22Item 6Var6 value
23Item 3Var3 value
24Item 8Var8 value
Sheet1
Cell Formulas
RangeFormula
G2:G24G2=IFERROR(INDEX(Sheet2!B:B,MATCH(Sheet1!C2,Sheet2!A:A,0)),"")


Temp.PNG
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,020
Office Version
  1. 2016
Platform
  1. Windows
@JackDanIce,

I wrote the code afterwards like this:

Code:
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.
 
Solution

Forum statistics

Threads
1,147,451
Messages
5,741,200
Members
423,648
Latest member
steel1968

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top