none
Expected: End of Statement Error RRS feed

  • Question

  • I am using Excel 2010, I want to loop through this range, but I get a Expected: End of Statement error at Line 2.

    1 Dim bCell As Range
    2     For Each bCell In Cells(2, 1) Cells(lRow2, 47)
    3        If IsEmpty(bCell) Then
    4            bCell = "'"
    5        End If
    6    Next

    Thanks


    Michael Morgan

    Monday, June 27, 2016 7:58 PM

Answers

  • Hi

    You declare bCell as range but what is Irow2. If I remove Irow and leave just the 2, it works for me.


    Cimjet

    • Marked as answer by m3looper Tuesday, June 28, 2016 1:42 PM
    Monday, June 27, 2016 11:30 PM
  • lRow2 is a global variable I use in several places and has been working fine. But you are right I tested it here with another local variable and it works fine.

    Thanks for the help.


    Michael Morgan

    • Marked as answer by m3looper Tuesday, June 28, 2016 2:01 PM
    Tuesday, June 28, 2016 1:42 PM

All replies

  • Do you want the range from Cells(2, 1) to Cells(lRow2, 47)? If so, line 2 should be

         For Each bCell In Range(Cells(2, 1), Cells(lRow2, 47))


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, June 27, 2016 8:39 PM
  • Hans,

    When I tried this option below as you mentioned, I got the Application-defined or object-defined error.

    Sheets("DecisionLog").Range("A2").Select
        Dim bCell As Range
         For Each bCell In Range(Cells(2, 1), Cells(lrow2, 47))
            If IsEmpty(bCell) Then
                bCell = "'"
            End If
        Next


    Michael Morgan

    Monday, June 27, 2016 10:11 PM
  • Hi

    You declare bCell as range but what is Irow2. If I remove Irow and leave just the 2, it works for me.


    Cimjet

    • Marked as answer by m3looper Tuesday, June 28, 2016 1:42 PM
    Monday, June 27, 2016 11:30 PM
  • Hi m3looper,

    you can try to use the code like below.

    Sub demo()
     Dim bCell, rng As Range
     Set rng = Range("2:1,2:47")
     
          For Each bCell In rng.Cells
          Debug.Print i
            If IsEmpty(bCell) Then
                bCell = "'"
             End If
         Next
    End Sub

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, June 28, 2016 4:33 AM
    Moderator
  • lRow2 as Long. It is a global variable that counts that last row and then is used in these procedures.

    Michael Morgan

    Tuesday, June 28, 2016 1:29 PM
  • Deepak,

    My reason for using Cells is so that I can use a variable like lRow2, because the last Row changes. 


    Michael Morgan

    Tuesday, June 28, 2016 1:34 PM
  • lRow2 is a global variable I use in several places and has been working fine. But you are right I tested it here with another local variable and it works fine.

    Thanks for the help.


    Michael Morgan

    • Marked as answer by m3looper Tuesday, June 28, 2016 2:01 PM
    Tuesday, June 28, 2016 1:42 PM