CODEWITHSUNDEEP
stringvbaexcel
InternInNeed
InternInNeed

Reputation: 159

VBA check if String element is empty

I am currently splitting strings containing 3 elements (a/b/c) from Worksheet 1 and pasting each part on Worksheet 2 in different columns. This is done by a Loop.

However, if the string has only 2 elements, so "c" from above is empty, I get a Runtime Error 9: "Index out of Range".

The string can and will sometimes only have 1 or 2 elements instead of all 3. Is there any way I can avoid this error?

My research led me to try If Len(Trim()) = vbnullstring and Len() = 0 but nothing worked.

Any help would be greatly appreciated!

For Each IDrow In wsInput.Range(IDcolLetter & "2:" & IDcolLetter & lastRow)

    'Fourthly, get the respective row-number for each skill
    IDrowNumber = Split(IDrow.Address, "$")(2)

    'Fifthly, split the strings in 3 parts
    Dim myElements() As String
    myElements = Split(wsInput.Range(IDcolLetter & IDrowNumber).value, "\")

    'Sixthly, for every skill of that supplier, copy the ID in A, CG in B, Category in C and Product in D
    NextRow = ws4.Range("A" & Rows.Count).End(xlUp).row + 1

    If Len(myElements(2)) = 0 Then <<<<<<<<<<<<<<<<<<<<<ERROR HERE<<<<<<<<<<<<<<<<<<<<<<<
          wsInput.Range(IDcolLetter & "1").Copy Destination:=ws4.Range("A" & NextRow) 'ID
          ws4.Range("B" & NextRow) = myElements(0)                                 'Commodity Group
          ws4.Range("C" & NextRow) = myElements(1)                                 'Category
    Else
          wsInput.Range(IDcolLetter & "1").Copy Destination:=ws4.Range("A" & NextRow) 'ID
          ws4.Range("B" & NextRow) = myElements(0)                                 'Commodity Group
          ws4.Range("C" & NextRow) = myElements(1)                                 'Category
          ws4.Range("D" & NextRow) = myElements(2)                                 'Product
    End If


Next IDrow

Upvotes: 0

Views: 288

Answers (3)

user6432984
user6432984

Reputation:

This is as much a code review as it is an answer.

  • It's best to declare all your variables at the top of the procedure

IDrow is a range object. Assuming IDrow.Address = "A100" then

  • Split(IDrow.Address, "$")(2) = 100
  • IDrow.Row = 100

These two values are also the same

  • wsInput.Range(IDcolLetter & IDrowNumber).value
  • IDrow.value

You are better off assigning the values of myElements by resizing the range to match the to number of element. This is more readable; because it cleans up a lot of repetitive code.

ws4.Range("B" & NextRow).Resize(1, UBound(myElements) + 1) = myElements

Both of these lines do the same thing. I am trying to promote the later pattern because I feel is cleaner and easier to read.

  • For Each IDrow In wsInput.Range(IDcolLetter & "2:" & IDcolLetter & lastRow)

  • For Each IDrow In wsInput.Columns(IDcolLetter).Rows("2:" & lastRow)

Dim myElements() As String

With wsInput

    For Each IDrow In wsInput.Columns(IDcolLetter).Rows("2:" & lastRow)
        'Fifthly, split the strings in 3 parts
        myElements = Split(IDrow.Value, "\")

        'Sixthly, for every skill of that supplier, copy the ID in A, CG in B, Category in C and Product in D
        NextRow = ws4.Range("A" & Rows.Count).End(xlUp).Row + 1

        .Range(IDcolLetter & "1").Copy Destination:=ws4.Range("A" & NextRow)
        ws4.Range("B" & NextRow).Resize(1, UBound(myElements) + 1) = myElements

    Next IDrow

End With

Upvotes: 2

user3598756
user3598756

Reputation: 29421

simply use

ws4.Range("B" & NextRow).Resize(, UBound(myElements)).Value = myElements

Upvotes: 1

Shai Rado
Shai Rado

Reputation: 33692

You can use the UBound(myElements) to get the number of elements in myElements array after the Split.

Add the following code to your existing code:

'Fifthly, split the strings in 3 parts
Dim myElements() As String
myElements = Split(wsInput.Range(IDcolLetter & IDrowNumber).Value, "\")

Dim arrCount    As Long
' count the upper bound limit of myElements array
arrCount = UBound(myElements)

' if there is an Element 3 (from Split function)
If arrCount >= 2 Then
    ' add the rest of your code here...

End If

Upvotes: 3

Related Questions