How to create a counter in Excel VBA

[shirleyj]

Dear All,

I am fairly new to VBA and I am having trouble getting the below code to run correctly.

I am trying to create a counter that will increment from 0 up to the maximum number set in the cell named “selected”. So if the cell “selected” contains the number 10, the cell “counter” will increase from 0 to 1 to 2, etc until it reaches the maximum of 10 and ideally would then loop back to 1 again.

When I run the code in ‘step into’ mode, it works fine, but when I run the entire code, it jumps straight ahead to 9 and I cannot figure out why.

Any help would be really appreciated!

Thank you for your time.

Best regards,


Shirley

sub counterincrement()

Dim a As Integer
Dim b As Integer
Dim c As Integer

' "selected" is the cell showing number of visible rows in a filtered list (eg 10). The counter below should not exceed this number
' "counter" is the name of the cell which should increment from 0 up to maximum number in selected cell


a = Range("selected").Value
b = Range("counter").Value
c = 0

Do

Range("counter").Value = c
c = c + 1
Loop Until c = a




End Sub

 

[Yahya]

Try this

Sub counterincrement()

Dim a As Integer
Dim c As Integer

' "selected" is the cell showing number of visible rows in a filtered list (eg 10). The counter below should not exceed this number
' "counter" is the name of the cell which should increment from 0 up to maximum number in selected cell


a = Range("selected").Value
c = 1

For c = 1 To a
    Range("counter").Value = c
Next c



End Sub

 

[mikerickson]

Perhaps

Sub test()
    Dim limitCell As Range
    Dim countingCell As Range
    Set limitCell = Sheet1.Range("A1")
    Set countingCell = Sheet1.Range("B2")

    With countingCell
        .Value = 1 + (Val(CStr(.Value)) Mod (Val(CStr(limitCell.Value))))
    End With
End Sub

 

[shirleyj]

Hi Yahya,

Thanks for your quick reply. I couldn't get this code to work either .... like in my code, it jumped straight ahead to the last item in the list, instead of incrementing through one by one. Do you know why this is happening?

Many thanks,

Shirley

 

[mikerickson]

Is the code that I posted doing what you want. You need to call it every time you want the cell incrimented.

 

[Scott Huish]

It's not jumping ahead, it is just incrementing so fast you don't see it happening. What you need is a delay in the code, if you are wanting to see it increment. Keep in mind you are not going to be able to do anything else while the code is running:

Sub test()
Dim c As Range, d As Range
Set c = Range("selected")
Set d = Range("counter")
Do
    d = (d Mod c) + 1
    Application.Wait Now() + TimeValue("00:00:01")
Loop
End Sub

Hit CTRL-BREAK to end it.

 

[shirleyj]

Hi Mikerickson,

Just posted you a response, but for some reason it was lost! I am trying again now.

Shirley

 

[shirleyj]

Hi Mikerickson,

Thank you for your message – and apologies for the delay in responding. I have just been trying to get your code to work in my spreadsheet. I created a new workbook with your code and it worked perfectly. Now just having troubles making it work in mine. As I am quite new to VBA, this is probably a very basic question ….. is it possible to set the variables with the cell name, ie the variable “limitCell” is located in a cell I have called “selected” and the variable “countingCell” is in a cell I have called “counter”. I tried substituting sheet1.range(“A1”) with workings.range(“d1”) – the name & cell address on my spreadsheet, but this created an error.

Many thanks in advance for your assistance.

Best regards,

Shirley

 

[shirleyj]

Hi HotPepper,

Thanks for your msg. Actually, as Mickerikson suggested, I do want to call it everytime to increment the cell. His code should work, once I understand how to set the variables to the cells I have named.

Thanks for your suggestion .... could still come in handy in another situation!

Best regards,

Shirley

 

[Scott Huish]

Then this should do it:

Sub test()
Dim c As Range, d As Range
Set c = Range("selected")
Set d = Range("counter")
d = (d Mod c) + 1
End Sub