VBA expected end of statement

Question

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

https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/whilewend-statement — BigBen, Dec 21 '21 at 14:30
A `For ... Next` loop would probably be better here. `IsNull(Cells(i, 35).Value)` - this doesn't do what you think it does. `IsEmpty` perhaps instead of `IsNull`. — BigBen, Dec 21 '21 at 14:30
`Dim i As Integer = 23` is also not valid syntax. Declare, then assign value. — SJR, Dec 21 '21 at 14:33
This `While` … `End While` is no valid syntax, you need to use `Do While` … `Loop` (see [Do...Loop statement](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/doloop-statement)). — Pᴇʜ, Dec 21 '21 at 14:37
Or [find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) and use a `For` ... `Next` loop. — BigBen, Dec 21 '21 at 14:38
@BigBen Just for the record "Last used cell" is not the same as "first blank cell" (what the OP does right now). So the outcome may be different. — Pᴇʜ, Dec 21 '21 at 14:46

Pᴇʜ · Answer 1 · 2021-12-21T14:57:30.357

3

You cannot declare a variable and set a value at the same time Dim i As Integer = 23
Row counts are of type Long not Integer, Excel has more rows than Integer can handle.
```
Dim i As Long 
i = 23
```
While … End While is no valid syntax, you need to use Do While … Loop (see Do...Loop statement).

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

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

edited Dec 21 '21 at 14:57

answered Dec 21 '21 at 14:43

Pᴇʜ

56,719
10
49
73

The VBA syntax for while is while/wend. However the use of this structure is deprecated in favour of the more flexible do loop construct. Also, when reading up on VBA do make sure that you haven't mistakenly landed on a Visual Basic (VB net) page – freeflow Dec 21 '21 at 17:41
@freeflow That's why I suggested the `Do` loop. – Pᴇʜ Dec 21 '21 at 19:59
1

@peh The way you explained wasn't very clear imho – freeflow Dec 21 '21 at 20:08

VBA expected end of statement

1 Answers1