0

All i am trying to get is the count right before an empty cell in the following code:

Dim l As Long
    Dim r As Long
    For l = 0 To 65866

    If IsEmpty(wb.Worksheets("Fund Position(URIL)").Range("A1")) Then
       Exit For
    Else
      r = r + 1
   End If
    Next l

    MsgBox r

The above code throws me an Automation error with a Runtime error : -2147221080 (800401a8) when it executes the if statement.

Please help!

Update:

I changed

If IsEmpty(wb.Worksheets("Fund Position(URIL)").Range("A1")) Then

to:

If IsEmpty(wb.Sheets("Fund Position(URIL)").Range("A1")) Then

and it worked. Could someone explain me why is that?

Community
  • 1
  • 1
Salik
  • 508
  • 6
  • 17
  • 35
  • I guess you are trying to find the last row in Col A which has data? If yes, then see [this](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) – Siddharth Rout Dec 18 '13 at 05:02
  • Actually, I am trying to find the first blank row in Col A. – Salik Dec 18 '13 at 05:03
  • first blank row can mean two things... Lets say `A1-A10` has data then `A11` is the blank cell that you want. Another scenarion. `A1-A5` has data and then A7-A10 has data. In this scenario do you want `A6` or `A11` – Siddharth Rout Dec 18 '13 at 05:05
  • In the latter scenario, where an empty cell occurs between two ranges of non-empty cell. That is my scenario. Hence, i would want A6. – Salik Dec 18 '13 at 05:07
  • You cannot keep `.Range("A1")` constant? Else how will you find which row is empty? See the answer that I posted – Siddharth Rout Dec 18 '13 at 05:14
  • I changed my code for simplicity. Originally, it checks cells dynamically based on the value of `l` . I knew there was no error there so i made it simple by keeping range constant. – Salik Dec 18 '13 at 05:16

1 Answers1

0

Is this what you are trying?

Sub Sample()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim l As Long

    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Fund Position(URIL)")

    For l = 1 To 65866
        With ws
            If IsEmpty(.Range("A" & l)) Then Exit For
        End With
    Next l

    MsgBox l
End Sub
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250