VBA Loop to get Cell Strings based on two criteria

aussie_striker

New Member
Joined
Jul 22, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
I have a workbook that a ladder and competition fixtures. What I am trying to do is get the next 3 rounds opposition to display on the Ladder sheet. I cannot think of anyway that I can do it using formulas so think vba is the better option. If you can think of a formula to do it I'm open to suggestions.

The excel2 image shows the ladder sheet and the result I am after noting that I have reduced the name down using a left command taking off the last 14 digits. I also added the current ladder position of the team using a index match lookup to the ladder. This would be the preferred format if achievable.

The excel1 image is the fixtures sheet. I'm thinking it will need to look up the row that matches the ladder sheet ie 4, 5, 6. as well as having the team corresponding to the one on the ladder. Then it has to determine if it is in column F or column H and return the opposite column result.

The loop would come into doing it for each team then for each round. I'm guessing a loop inside a loop to achieve it.

Hope that makes sense.
 

Attachments

  • Excel1.png
    Excel1.png
    71.3 KB · Views: 2
  • Excel2.png
    Excel2.png
    80.1 KB · Views: 2

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,597
Messages
5,838,265
Members
430,536
Latest member
Manoj Gaidhankar

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