CODEWITHSUNDEEP
vbaexcel
Norbert
Norbert

Reputation: 63

How can I write a code where the values are 'stepped'?

I just started programming in Excel VBA and I have a question.

How can I write a code where the values are 'stepped'?

I want to code this by using do until, do while and for only.

I'm using two variables: x = 1 and y = 1. The value must be the number of the cycle.

The output would look like this:

cells(x,y). value = a
cells(2x,y). value = 2a
cells(2x,2y). value = 3a
cells(3x,2y). value = 4a
cells(3x,3y). value = 5a
cells(4x,3y). value = 6a
cells(4x,4y). value = 7a

Upvotes: 1

Views: 126

Answers (3)

Pᴇʜ
Pᴇʜ

Reputation: 57733

This would be an alternative version to do it

Option Explicit

Public Sub Generate()
    Dim a As Long: a = 1

    Dim i As Long
    For i = 1 To 7
        Cells((i \ 2) + 1, ((i - 1) \ 2) + 1).Value = i * a
    Next i
End Sub

Note that this uses the div Operator 5 \ 2 which is not a standard division 5 / 2. The div operator divides two numbers and returns an integer result.

  • Standard division of 5 / 2 = 2.5
  • Div Operator of 5 / 2 = 2

So (i \ 2) + 1, ((i - 1) \ 2) + 1 results in

1, 1 
2, 1 
2, 2 
3, 2 
3, 3 
4, 3 
4, 4

Upvotes: 1

SJR
SJR

Reputation: 23081

Here is a different approach. This will give you an extra row (5x,4y) so if this is not desired can be excluded using an If statement.

Sub Generate()

Dim x As Long, y As Long, a As Long, b As Long, c As Long

For b = 1 To 4
    Cells(b * x, b * y) = (2 * b - 1) * a
    Cells((b + 1) * x, b * y) = 2 * b * a
Next b

End Sub

Upvotes: 1

JvdV
JvdV

Reputation: 75900

You can step through your sequence in VBA using arrays like so:

Sub StepAXY()

Dim Z As Long, A As Long, X As Long, Y As Long
Dim arrA() As Variant, arrX() As Variant, arrY() As Variant

arrA = Array(1, 2, 3, 4, 5, 6, 7)
arrX = Array(1, 2, 2, 3, 3, 4, 4)
arrY = Array(1, 1, 2, 2, 3, 3, 4)

For Z = 1 To 7 Step 1
    A = arrA(Z - 1)
    X = arrX(Z - 1)
    Y = arrY(Z - 1)
    Cells(X * 1, Y * 1) = A * 1
Next Z

End Sub

Upvotes: 1

Related Questions