Include a counter in a VBA macro

[RCONDADO]

Dear all,

I'm using a macro just to suffle a sequence of numbers and then assign that to a button (attached), as follow:

Sub Embaralha_seleção()
    Dim numm()
    numm = Range("A1:A100")
    ct = UBound(numm, 2)
    lt = UBound(numm, 1)
    ReDim dex(1 To lt * ct)
    n = 0
    For h = 1 To ct
        For v = 1 To lt
            If numm(v, h) & " " <> " " Then
                n = n + 1
                dex(n) = numm(v, h)
                numm(v, h) = ""
            End If
        Next
    Next
    t = 1
    For h = 1 To ct
        For v = 1 To lt
volta:
            Randomize
            vvv = Int((n * Rnd) + 1)
            If dex(vvv) = "" And t <= n Then
                GoTo volta
            Else
                t = t + 1
                numm(v, h) = dex(vvv)
                dex(vvv) = ""
            End If
        Next
    Next
    Range("A1:A100") = numm
End Sub

It's working properly, but now I need to include two rotines, as follow:

1. Include a counter to count how many times the button was pressed; and
2. Save the workbook everytime that the button was pressed.

Please, could you help me?
Thank you and regards!

R. Condado.

 

[RCONDADO]

In addion, I am trying to run this macro in another column, in the same code. For example, this code runs into the column A, but I want that it runs into the column B, C, D, etc. Can I include in the same code or should I create a new sentece for that?

 

[Peter_SSs]

Do you mean that you have numbers in several columns and you want to shuffle each column independently?

Is there a specific number of columns? Or how do we determine how many columns?

After the columns of numbers is there then a blank column to use as a helper?

 

[RCONDADO]

Yes, I need to shuffle each column independently.
Actually, I need to shufle 80 independently columns, how you can see on the pictute attached.
Initialy, I was shuffering only the numbers in the column A, but now I need to shuffle the others columns too.

 

[Peter_SSs]

This looks very different as not all columns have data and those that do do not all have numerical data.
It also looks like different columns have different numbers of rows filled.
There also appears to be three very distinct sections of the sheet at the top rows 1:2 then rows $:5 and then below row 8.

You will need to set out in much more precise detail exactly what you want to do.

 

[RCONDADO]

Hi Peter,

Yes, you're wright. This is why I had to formulate one sentence for each one of the 80 columns, as follow attached and bellow:

Arr = Range("AK10:AE22")
Randomize
For Cnt = UBound(Arr) To 1 Step -1
RandIdx = Int(Cnt * Rnd + 1)
Temp = Arr(RandIdx, 1)
Arr(RandIdx, 1) = Arr(Cnt, 1)
Arr(Cnt, 1) = Temp
Next
Range("AK10:AE22") = Arr

Arr = Range("AN10:AN23")
Randomize
For Cnt = UBound(Arr) To 1 Step -1
RandIdx = Int(Cnt * Rnd + 1)
Temp = Arr(RandIdx, 1)
Arr(RandIdx, 1) = Arr(Cnt, 1)
Arr(Cnt, 1) = Temp
Next
Range("AN10:AN23") = Arr

Arr = Range("AQ10:AQ24")
Randomize
For Cnt = UBound(Arr) To 1 Step -1
RandIdx = Int(Cnt * Rnd + 1)
Temp = Arr(RandIdx, 1)
Arr(RandIdx, 1) = Arr(Cnt, 1)
Arr(Cnt, 1) = Temp
Next
Range("AQ10:AQ24") = Arr

Now it's working.
One more time, many thanks for your help!

 

[Peter_SSs]

Now it's working.

Glad you got it going.

For the future, when posting vba code, please use code tags to preserve the formatting. My signature block below has help on that.