Include a counter in a VBA macro

RCONDADO

New Member
Joined
Nov 4, 2014
Messages
12
Office Version
365
Platform
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

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,080
Office Version
365
Platform
Windows
You can store a value in a hidden (or very hidden) sheet and increment that at each press of the button. The save depends on if the book has already been saved previously and when you want it to save. Probably something like:

VBA Code:
Sheets("VeryHidden").Range("A1").Value = Sheets("VeryHidden").Range("A1").Value + 1
ThisWorkbook.Save
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,031
Office Version
2010
Platform
Windows
The code you posted simply randomizes the values in range A1:A100, correct? If so, you can use this shorter routine to do the same...
VBA Code:
Sub Embaralha_seleção()
  Dim Cnt As Long, RandIdx As Long, Temp As Variant, Arr As Variant
  Arr = Range("A1:A100")
  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("A1:A100") = Arr
End Sub
The above code does not address your two additional questions. It is unclear what you want done with the count of the number of times the button was pressed. Is it to be saved somewhere or incorporated into the name used to save the workbook under, or used in some other way. Also, in addition, do you want the button count to persist from closing of the current workbook to the next time the workbook is open or should the button count start back at zero each time the workbook is open. Please clarify these for us?
 

RCONDADO

New Member
Joined
Nov 4, 2014
Messages
12
Office Version
365
Platform
Windows
You can store a value in a hidden (or very hidden) sheet and increment that at each press of the button. The save depends on if the book has already been saved previously and when you want it to save. Probably something like:

VBA Code:
Sheets("VeryHidden").Range("A1").Value = Sheets("VeryHidden").Range("A1").Value + 1
ThisWorkbook.Save
Hi @steve the fish,
Yes, this is exactly what I need to do. I just need the right code to do that.
 

RCONDADO

New Member
Joined
Nov 4, 2014
Messages
12
Office Version
365
Platform
Windows
The code you posted simply randomizes the values in range A1:A100, correct? If so, you can use this shorter routine to do the same...
VBA Code:
Sub Embaralha_seleção()
  Dim Cnt As Long, RandIdx As Long, Temp As Variant, Arr As Variant
  Arr = Range("A1:A100")
  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("A1:A100") = Arr
End Sub
The above code does not address your two additional questions. It is unclear what you want done with the count of the number of times the button was pressed. Is it to be saved somewhere or incorporated into the name used to save the workbook under, or used in some other way. Also, in addition, do you want the button count to persist from closing of the current workbook to the next time the workbook is open or should the button count start back at zero each time the workbook is open. Please clarify these for us?
Hi @Rick Rothstein,
Thank you for your comments.
Actually, this image attached is the button that I created an I just have to include a line bellow to inform "Count" and how many times the button was pressed and I want the button count to persist from closing of the current workbook to the next time the workbook is open.
Do you think is it possible?
 

Attachments

RCONDADO

New Member
Joined
Nov 4, 2014
Messages
12
Office Version
365
Platform
Windows
You can store a value in a hidden (or very hidden) sheet and increment that at each press of the button. The save depends on if the book has already been saved previously and when you want it to save. Probably something like:

VBA Code:
Sheets("VeryHidden").Range("A1").Value = Sheets("VeryHidden").Range("A1").Value + 1
ThisWorkbook.Save
@steve the fish

It worked now with your code.
Many thanks for your help!
 

RCONDADO

New Member
Joined
Nov 4, 2014
Messages
12
Office Version
365
Platform
Windows
The code you posted simply randomizes the values in range A1:A100, correct? If so, you can use this shorter routine to do the same...
VBA Code:
Sub Embaralha_seleção()
  Dim Cnt As Long, RandIdx As Long, Temp As Variant, Arr As Variant
  Arr = Range("A1:A100")
  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("A1:A100") = Arr
End Sub
The above code does not address your two additional questions. It is unclear what you want done with the count of the number of times the button was pressed. Is it to be saved somewhere or incorporated into the name used to save the workbook under, or used in some other way. Also, in addition, do you want the button count to persist from closing of the current workbook to the next time the workbook is open or should the button count start back at zero each time the workbook is open. Please clarify these for us?
@Rick Rothstein,
Thank you, your shorter code worked perfecly.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,137
Office Version
365
Platform
Windows
Here is another short way to shuffle the 100 rows. Shorter if column B is empty as two lines of code can be removed.

VBA Code:
Sub Shuffle()
  Columns("B").Insert '<- delete if column B is empty
  Range("B1:B100").Formula = "=rand()"
  Range("A1:B100").Sort Key1:=Range("B1"), Header:=xlNo
  Columns("B").Delete '<- delete if column B is empty
End Sub
 

RCONDADO

New Member
Joined
Nov 4, 2014
Messages
12
Office Version
365
Platform
Windows
Here is another short way to shuffle the 100 rows. Shorter if column B is empty as two lines of code can be removed.

VBA Code:
Sub Shuffle()
  Columns("B").Insert '<- delete if column B is empty
  Range("B1:B100").Formula = "=rand()"
  Range("A1:B100").Sort Key1:=Range("B1"), Header:=xlNo
  Columns("B").Delete '<- delete if column B is empty
End Sub
@Peter_SSs Thank you
 

Watch MrExcel Video

Forum statistics

Threads
1,102,153
Messages
5,485,059
Members
407,480
Latest member
breederbulldog

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top