Results 1 to 3 of 3

Thread: VBA loops

  1. #1
    New Member
    Join Date
    Jun 2016
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA loops

    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!

  2. #2
    Board Regular
    Join Date
    Dec 2017
    Location
    UK
    Posts
    928
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA loops

    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
    Speed up your code use variant arrays and NEVER ACCESS THE WORKSHEET IN A LOOP

  3. #3
    New Member
    Join Date
    Jun 2016
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA loops

    Offthelip, you are the best! This is exactly what I needed! Thanks for your time and help.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •