creating dynamic for loop in VBA excel

Question

I have tried to create dynamic for loop with the start and end numbers being variables. However, the code cannot get into the for loop with the changed start and end numbers. How could I solve my problem?

Here is my code

Sub row_column()

Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("origin_boundary")

Row = 1

Dim Start As Integer
Start = 2
Dim Last As Integer
Last = 41

For x = Start To (Last + 1)

If ws.Cells(x, 1) <= Row * 40 Then
ws.Cells(x, 6) = Row

Else
If ws.Cells(x, 1) > (Row * 40) Then

Row = Row + 1
ws.Cells(x, 6) = Row
Start = (Row - 1) * 40 + 2
Last = Start + 39

End If

End If

Next x






End Sub

What is the expected outcome? Code as is will go through a For loop. — QHarr
– QHarr, Commented Oct 24, 2017 at 8:05

Tehscript · Accepted Answer · 2017-10-24 11:46:05Z

1

For x = Start To (Last + 1) loops x from Start value to (Last + 1) value.

The first time loop runs, x = 2 because Start = 2.
The second time loop runs, x = 3 because 3 comes after 2. Start
doesn't have a role here so there is no point changing the
value of Start. Instead, you can change the value of x like x = (Row - 1) * 40 + 2.
Last = Start + 39 can be dynamic if you want, but it won't matter until x reaches the value of (dynamically changed) Last value. You can use x instead of Start here.

answered Oct 24, 2017 at 11:46

Tehscript

2,5562 gold badges15 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

creating dynamic for loop in VBA excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related