CODEWITHSUNDEEP
vbaexcel
Jay F
Jay F

Reputation: 58

VBA crashes Excel

I'm using excel and VBA to append a cell using the contents of the below table. The data is being gathered from Table 1. It the code first checks what the option in column B(2) is op1 it should then read the contents of column A(1) and append the cell D2 (Below op1 in Table 2).

This is then using a loop to go through all the values within Table 1 which then should continuously populate Table 2 until the set limit (3) is exceeded.

Table 1:

A       B
---    ---
text    op1
textt   op2
text    op1

Table 2:

D
---
op1
*** The Cell I want to Append ***

The code I'm using is as follows.

Sub ProcOne()
    OpColOne = 4

    TextColMain = 1
    OpColMain = 2

    i = 1

    Do Until i > 3
        If Cells(i, OpColMain) = "op1" Then
            Cells(2, OpColOne) = Cells(2, OpColOne) & vbNewLine & Cells(i, TextColMain)
            i = i + 1
        End If
    Loop
End Sub

Upvotes: 2

Views: 101

Answers (1)

Vityata
Vityata

Reputation: 43593

Option Explicit

Sub ProcOne()

    Dim OpColMain   As Long
    Dim TextColMain As Long
    Dim opColOne    As Long
    Dim i           As Long

    opColOne = 4

    TextColMain = 1
    OpColMain = 2

    i = 1

    Do Until i >= 3
        With ActiveSheet

            If .Cells(i, OpColMain) = "op1" Then
                .Cells(2, opColOne) = .Cells(2, opColOne) & vbNewLine & .Cells(i, TextColMain)
            End If
            i = i + 1

        End With
    Loop

End Sub

You need Option Explicit, to make sure that you declare all variables correctly. You need With ActiveSheet, because without it, you may be getting an error in Excel. Thus, you declare .Cells to the ActiveSheet and not just Cells. See the Microsoft info here: Worksheet.Cells Property (Excel).

Last but not least i = i + 1 should be outside the loop, to make sure that we leave the endless loop. Or inside, depending on the code and the case. But in this case when we only have 3 lines - outside.

Final edit - if you want to loop 3 times, use Do Until i >= 3, otherwise you loop only twice.

Upvotes: 3

Related Questions