Include a counter in a VBA macro

RCONDADO

New Member
Joined
Nov 4, 2014
Messages
13
Office Version
  1. 365
Platform
  1. Windows
Dear all,

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

VBA Code:
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.
 

Attachments

  • Capture.PNG
    Capture.PNG
    17 KB · Views: 13
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?
 
Upvote 0

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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?
 
Upvote 0
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.
 

Attachments

  • shuffle.png
    shuffle.png
    49.1 KB · Views: 7
Upvote 0
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.
 
Upvote 0
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

1585956465570.png


Now it's working.
One more time, many thanks for your help!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

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