VBA check if String element is empty

Question

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

try the code in my answer below

Shai Rado
– Shai Rado

2016-09-08 06:31:11 +00:00
Commented Sep 8, 2016 at 6:31 — Shai Rado
– Shai Rado, Commented Sep 8, 2016 at 6:31

Shai Rado · Accepted Answer · 2016-09-08 06:29:45Z

3

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

answered Sep 8, 2016 at 6:29

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

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

10 Comments

user3598756 Over a year ago

@Intermind, do you really prefer to use an If-Then block (plus additional variables) instead of a single line (see my answer)? Keep this answer accepted, but please get accustomed to "lean" code...

InternInNeed Over a year ago

@user3598756 honestly I do not prefer anything because I started with VBA about 6 weeks ago and thus do not know the differences between each method. So far I am just happy that it works.Moreover, I did not know how to implement your solution into my code.

user3598756 Over a year ago

you just substitute your code from If Len(myElements(2)) = 0 Then... to End If with that simple line...

Shai Rado Over a year ago

@InternInNeed so after all the pressure you were put on you gave up and removed my "Answer" ?

Shai Rado Over a year ago

@InternInNeed it's ok, let me know if you need help fixing it

|

score 2 · Accepted Answer · 2016-09-14 09:36:36Z

2

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

edited Sep 14, 2016 at 9:36

answered Sep 8, 2016 at 7:06

user6432984

11 Comments

InternInNeed Over a year ago

Thank you for taking your time for this extensive Elaboration. I am always happy to learn and I will adjust my future code this way. One question though: Would this code post all 3 string-elements or would I need ws4.Range("B" & NextRow).Resize(1, UBound(myElements)) = myElements(0) , ws4.Range("C" & NextRow).Resize(1, UBound(myElements)) = myElements(1) and ws4.Range("D" & NextRow).Resize(1, UBound(myElements)) = myElements(2) ?

InternInNeed Over a year ago

Thomas, I just adapted your Approach and it does not include the 3rd part of the string - "Products". Do you have any idea why? See here: imgur.com/a/EWnRp

user6432984 Over a year ago

Sorry about that. Because myElements is a zero base array,I should have added 1 to the Ubound of myElements: ws4.Range("B" & NextRow).Resize(1, UBound(myElements) + 1)

InternInNeed Over a year ago

Thank you again for your help!

InternInNeed Over a year ago

Thomas, my apologies for bothering you again. Is it possible to set a definitie range to cells B2, C3 and D4, and save the elements there, thereby using the resize-function? I am having Trouble working with strings that only have 1 or 2 elements..

|

user3598756 · Accepted Answer · 2016-09-08 06:34:48Z

1

simply use

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

answered Sep 8, 2016 at 6:34

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Collectives™ on Stack Overflow

VBA check if String element is empty

3 Answers 3

10 Comments

11 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

10 Comments

11 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related