Referencing Multiple Cells Within A Loop (VBA)

daveh21

New Member
Joined
May 29, 2015
Messages
3
Hi guys,
Think this is a relatively simple fix, but have not come across the solution online yet, so apologies if I have missed something obvious!
I am looking at labeling each variable in a loop.
So we have a very basic table, such as :
<code style="font-family: monospace, monospace; margin: 0px 2px; border: 0px; border-radius: 2px; word-break: normal; display: block; font-size: 1em; line-height: 1.42857142857143em; padding: 0px !important; background-color: transparent;"> A B C (just uses a Countif formula in here fyi)

London London1

London London2


London London3
</code>With this VBA code for it:
<code style="font-family: monospace, monospace; margin: 0px 2px; border: 0px; border-radius: 2px; word-break: normal; display: block; font-size: 1em; line-height: 1.42857142857143em; padding: 0px !important; background-color: transparent;">Sub TEST()
LDN_Count= Application.WorksheetFunction.CountIf(Sheets("Sheet1").Range("B1:B10"), "London")
Dim i As Integer
For i = 1 To LDN_Count
"LDN_Locations_"&i = Application.WorksheetFunction.Match("London" & i, Sheets("Sheet1").Range("C1:C10"), 0)
Next i
End Sub
</code>The incorrect part is "LDN_Locations_"&i, but do not know how to label this without generating an error so that I can have a list of locations, I.e. LDN_Location_1 = 2, LDN_Location_2 = 4, LDN_Location = 7... So that I can use them later on in the code.
Any help much appreciated!! Thanks!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Forum statistics

Threads
1,203,754
Messages
6,057,156
Members
444,908
Latest member
Jayrey

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
Back
Top