CODEWITHSUNDEEP
vbaexcelfor-loop
Арука Батырбаева
Арука Батырбаева

Reputation: 31

Can't understand how to use for...next loops in Excel VBA

I know that this code can be easily rewritten to use a loop, but I can't.

k1 = (exk - 3 * increment)
k2 = k1 + 0.01
k3 = k2 + 0.01
k4 = k3 + 0.01
k5 = k4 + 0.01
k6 = k5 + 0.01
k7 = k6 + 0.01
Cells(7, 2).Value = k1

Cells(8, 2).Value = k2

Cells(9, 2).Value = k3

Cells(10, 2).Value = k4

Cells(11, 2).Value = k5

Cells(12, 2).Value = k6

Cells(13, 2).Value = k7

Upvotes: 2

Views: 113

Answers (4)

Dy.Lee
Dy.Lee

Reputation: 7567

one dimension array sample

Sub test()
    Dim exk, increment
    Dim vResult()
    Dim i As Integer
    exk = 5  'setting your value
    increment = 2 'setting your value

    ReDim vResult(1 To 7) 'array one dimension
    vResult(1) = exk - 3 * increment
    For i = 2 To 7
        vResult(i) = vResult(i - 1) + 0.01
    Next i
    Range("b7").Resize(7) = WorksheetFunction.Transpose(vResult)

End Sub

Upvotes: 0

mgae2m
mgae2m

Reputation: 1142

Here you are:

Sub Rewrite_Code()
    Dim k() As Double
    Dim i As Integer
    Const x = 7
    ReDim k(x)

    k1 = (exk - 3 * increment)
    'Redim Preserve k(i+1) '(this can using when program situation interactively changes)
    For i = 1 To x
        k(i + 1) = k(i) + 0.01
        ActiveSheet.Cells(i + 6, 2).Value = k(i)
    Next i
End Sub 'Rewrite

Upvotes: 1

paul bica
paul bica

Reputation: 10705

Option Explicit

Public Sub ForNext()
    Dim ws As Worksheet, r As Long, arr As Variant, exk As Long, increment As Long

    Set ws = ThisWorkbook.Worksheets("Sheet1")      'set the name of your Worksheet

    exk = 5 
    increment = 1

    arr = ws.Range(ws.Cells(7, 2), ws.Cells(13, 2)) 'copy range to array

    arr(1, 1) = (exk - 3) * increment               'set first array item

    For r = 2 To 7
        arr(r, 1) = arr(r - 1, 1) + 0.01            'set the rest of the items
    Next

    ws.Range(ws.Cells(7, 2), ws.Cells(13, 2)) = arr 'copy array back to range
End Sub
  • copy the range to array (B7:B13); the first item in array (B7) starts at index 1 - arr(1, 1)
  • set the first element in the array to your formula: arr(1, 1) = (exk - 3) * increment
  • iterate over the rest of the array, increment each element based on the previous value
  • place the array back on the range

Upvotes: 0

Variatus
Variatus

Reputation: 14383

Perhaps this is what you mean?

Const FirstRow As Long = 7
Dim K1 As Double
Dim i As Long

K1 = 13                 ' your formula is (exc - 3) * increment
For i = 1 To 7
    Cells(i + FirstRow - 1, 2).Value = K1
    K1 = K1 + 0.01
Next i

Upvotes: 0

Related Questions