VBA loops

tpthatsme

New Member
Joined
Jun 2, 2016
Messages
10
Hello all,

I have a macro that works, but I am trying to make it a little more efficient so that when changes happen, it will be easier to update in the future. The main idea is that I call for a file to open, do some stuff and then save and close it. The problem is that I have to do this with 16 files. I don't want to have 16 blocks of code if I can loop it with some small changes.

Here is an example of the file names:
Blue Hearts.csv
Blue Spades.csv
Blue Diamonds.csv
Blue Clubs.csv

Red Hearts.csv
Red Spades.csv
...and so on where the first word in the file name is a list like Blue, Red, Green, Yellow and the second word is a list like Hearts, Spades, Diamonds, Clubs.

Is there a way to do something like this?

For i= blue to yellow
For j= hearts to clubs
open file i+j.csv
do something great
save file i+j.csv
next
next

Above is just a way to explain what I am looking for, I know there is nothing correct about it as VBA.

Thanks!
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,043
Office Version
2010
Platform
Windows
this code shows you how to generate the file names:
Code:
Sub test()
 colr = Array("Blue", "Red", "Green", "Yellow")
 suit = Array("Clubs", "Diamonds", "Hearts", "Spades")
  For i = 0 To 3
   For j = 0 To 3
    fname = colr(i) & " " & suit(j) & ".csv"
    MsgBox fname
   Next j
  Next i


End Sub
 

tpthatsme

New Member
Joined
Jun 2, 2016
Messages
10
Offthelip, you are the best! This is exactly what I needed! Thanks for your time and help.
 

Forum statistics

Threads
1,086,098
Messages
5,387,820
Members
402,081
Latest member
PiotrX

Some videos you may like

This Week's Hot Topics

Top