VBA Continue If Array Empty

Question

I am just wondering how i can skip over an error if the array is not full? For example, 1 loop goes over whether the array has a first and last name, if there is no last name i would like the script to continue.

FullName = ActiveSheet.Cells(37, ii).Value
Name = Split(FullName, " ")

For intCount = LBound(Name) To UBound(Name)
  sData.Range("C" & iii).Value = Name(0)
  sData.Range("D" & iii).Value = Name(1)
Next

If Name(1) is empty then how can the code continue?

FYI that `intCount` loop appears to be completely redundant; the second iteration would simply overwrite everything the first one did. — Mathieu Guindon, Feb 21 '18 at 21:34

Mathieu Guindon · Accepted Answer · 2018-02-21T21:22:42.437

Since the two columns are consecutive, you can just paste the array in-place, using Range.Resize to dump the array across as many columns as needed - the only thing to watch for is if Name can contain more than a single space:

FullName = ActiveSheet.Cells(37, ii).Value
Name = Split(FullName, " ")
If UBound(Name) <= 1 Then
    sData.Range("C" & iii).Resize(, UBound(Name) + 1).Value = Name
Else
    'there was more than one space...
End If

QHarr · Answer 2 · 2018-02-22T05:13:09.333

With some test values

Option Explicit

Sub test()

Dim ii As Long
Dim iii As Long

ii = 2
iii = 3

Dim FullName As String
Dim Name() As String

With ActiveSheet

    FullName = .Cells(37, ii).Value

    If InStrRev(FullName, " ", -1) > 0 Then   'space present

        Name = Split(FullName, " ")

        If UBound(Name) > 1 Then Exit Sub 'there was more than one space present. Handling this was not specified so exit sub.

        .Range("C" & iii).Value = Name(0)
        .Range("D" & iii).Value = Name(1)

    Else

         .Range("C" & iii).Value = FullName
         .Range("D" & iii).Value = vbNullString

    End If

End With

End Sub

MoondogsMaDawg · Answer 3 · 2018-02-21T21:37:46.510

If you want to avoid using On Error Resume Next, you can try this:

FullName = ActiveSheet.Cells(37, ii).Value
Name = Split(FullName, " ")

If Len(Join(Name)) > 0 Then
    sData.Range("C" & iii).Value = Name(0)
    sData.Range("D" & iii).Value = Name(1)
End If

which was originally posted here. Join essentially reverts to the FullName value but without the space. Alternatively, you could just use If InStr(1, FullName, " ", vbBinaryCompare) > 0 Then.

VBA Continue If Array Empty

3 Answers3