Find First Empty Element in a String Array

Question

I am trying to use this code to determine the value of Firstcell but can't get an output:

Sub FindFirstEmptyCell()

    Dim l As Integer
    Dim Firstcell As Integer
    Dim MyArray(5) As String
    Dim currentRowValue As String

    MyArray(1) = "g"
    MyArray(2) = "g"
    MyArray(3) = "s"
    MyArray(4) = ""
    MyArray(5) = "f"


    For l = 0 To 5
            currentRowValue = MyArray(l)
        If IsEmpty(currentRowValue) = True Or currentRowValue = "" Then
             Firstcell = l: Exit For
        End If
    Next

End Sub

what do you mean by "output" , do you mean you are not getting the expected result for the variable Firstcell ? — Shai Rado
– Shai Rado, Commented Nov 15, 2016 at 12:29
not clear what's your real aim: are you looking for the some first empty cell in a Range? — user3598756
– user3598756, Commented Nov 15, 2016 at 14:09

Shai Rado · Accepted Answer · 2016-11-15 12:47:55Z

1

In your code you defined Dim MyArray(5) As String , which means MyArray has 6 elements, starting from 0 to 5. If you want to have 5 elements, change to Dim MyArray(4) As String.

So when setting strings to MyArray, you need to start from MyArray(0) = "g" , and so on...

Then, when running the code below, you need to remember that the array counter starts from 0, and if you want to get a reference to a row in a worksheet, then you need to add +1 , since there is no row 0, and you will get an error.

Sub FindFirstEmptyCell()

    Dim l As Integer
    Dim Firstcell As Integer
    Dim MyArray(4) As String
    Dim currentRowValue As String

    MyArray(0) = "g"
    MyArray(1) = "g"
    MyArray(2) = "s"
    MyArray(3) = ""
    MyArray(4) = "f"

    ' loop through all elements in MyArray
    For l = LBound(MyArray) To UBound(MyArray)
        If IsEmpty(MyArray(l)) Or MyArray(l) = "" Then
            Firstcell = l
            MsgBox "First empty element in MyArray is " & Firstcell
            Exit For
        End If
    Next

End Sub

answered Nov 15, 2016 at 12:47

Shai Rado

33.7k7 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3598756 Over a year ago

just to throw in that by declaring the upper bound of an array only the lower bound is implicitly set to the ruling Option Base. This means that should Option Base 1 be ruling then MyArray(4) is compiled as MyArray(1 to 4). To override this default behavior just declare both lower and upper bounds (MyArray(0 to 4)`).

Nathan_Sav · Accepted Answer · 2016-11-15 12:25:50Z

0

MyArray(0) is always "" by the looks of the code

answered Nov 15, 2016 at 12:25

Nathan_Sav

8,5762 gold badges18 silver badges22 bronze badges

Collectives™ on Stack Overflow

Find First Empty Element in a String Array

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related