CODEWITHSUNDEEP
excelvbacompiler-errors
Suni Hj
Suni Hj

Reputation: 1

VBA expected end of statement

I am trying to edit my excel table with VBA but an error appears while compiling. It doesnt recognize line 2 and line 10.

Sub IfThenElse()
    Dim i As Integer = 23
    While Not IsNull(Cells(i, 35).Value)
        If Cells(i, 35).Value > 1E+16 Then
            Cells(i, 4).Value = Cells(i, 35).Value / 10
        Else
            Cells(i, 4).Value = Cells(i, 35).Value
            i = i + 1
        End If
    End While
End Sub

Upvotes: 0

Views: 1229

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

  1. You cannot declare a variable and set a value at the same time Dim i As Integer = 23

  2. Row counts are of type Long not Integer, Excel has more rows than Integer can handle.

    Dim i As Long 
i = 23

  3. While … End While is no valid syntax, you need to use Do While … Loop (see Do...Loop statement).

  4. It is very unlikely that a cell value is Null if you are looking for an empty cell use IsEmpty or check for vbNullString

    Do While Not IsEmpty(Cells(i, 35).Value) 'or Do While Not Cells(i, 35).Value = vbNullString
    If Cells(i, 35).Value > 1E+16 Then
        Cells(i, 4).Value = Cells(i, 35).Value / 10
    Else
        Cells(i, 4).Value = Cells(i, 35).Value
        i = i + 1
    End If
Loop

  5. Not sure what exactly you are doing but i = i + 1 might need to come after End If.

Upvotes: 3

Related Questions