EXCEL LOOP....

[fourty]

Hi Champs,
Im using VBA for Excel. and
Im trying to use the code below to move through records and increment their values by one since they meet a certain criteria

Sub promotion()
Dim x As Range
Dim y As Integer
Sheet2.Range("c2").Activate

Do While y < 1002
y = ActiveCell.Row
Set x = Sheet2.Cells(y, 3)
x.Value = x.Value + 1
y = y + 1
ActiveSheet.Cells(y, 3).Activate
Loop

End Sub

Except the records are too many and the process is abit slower, Can anyone show me a better way to implement this.

[fourty]

In simple terms, i need to add 1 to whatever is in the cell c2 and move to the next until i reach cell c1001 then stop. I Know this is simple for and excel expert.

[Stuugie]

What is the criteria for incrementing?

[Stuugie]

Without criteria the following will do it for you:

Sub IncrementCells()

    Dim wS1 As Worksheet
    Dim cRng As Range, oC As Range

    Set wS1 = Worksheets("Sheet1")
    Set cRng = wS1.Range("C2:C1001")
    For Each oC In cRng
        oC.Value = oC.Value + 1
    Next oC

End Sub

[fourty]

Hi Stuugie,
Here we go again hehe.
I already tried to implement this code which is similar to yours above

Sub promotion()
Dim ws As Worksheet
Dim cl, region As Range

Set ws = Sheet2
Set region = ws.Range("c2:c1001")

For Each cl In region   ' Iterate through each element.
    If IsNumeric(cl) Then    ' If Text equals "Hello".
        cl.Value = cl.Value - 1  ' Set Found to True.
        Else
        Exit For    ' Exit loop.
    End If
Next cl
MsgBox "update completed successfully"
End Sub

except the process is too slow still. I can literally see how it changes from cell to cell since i work on multiple windows.
As i said the goal is to increment all values within that range (c2:c1001) by 1. Technically im trying to promte students who score above a given point into the next class.

Can you help me more?

Thanks again Stuugie.

[fourty]

By the way..
is there a statement logic that would instead of 'for each', implement the logic 'for all'
just a thought..

[fourty]

I figured it out already.
My records are referenced by some huge table data in another worksheet.
So i turned worksheet calculation to manual and enabled in the code.
Thanks anyways Stuugie. You are always my number 1 replier.

[Stuugie]

I forgot to set the screen updating, before and after all the work in the procedure gets done.

    Sub promotion()
    Dim ws As Worksheet
    Dim cl, region As Range

    Application.ScreenUpdating = False
    Set ws = Sheet2
    Set region = ws.Range("c2:c1001")
    For Each cl In region ' Iterate through each element.
    If IsNumeric(cl) Then ' If Text equals "Hello".
    cl.Value = cl.Value - 1 ' Set Found to True.
    Else
    Exit For ' Exit loop.
    End If
    Next cl
    MsgBox "update completed successfully"
    Application.ScreenUpdating = True
    End Sub

This will help to speed up the process a bit too.

Don't forget to mark this thread as solved if your issue has been resolved.