VBA Extract Strings Between Two Characters

Question

I am trying to extract the text between two occurrences of sub strings in a string using the character @ as a marker. I know there are 8 occurrences of @. I want to loop through the main string, and write the sub strings to the sheet.

Although I provided a Dim expression for the string textBetween I get the error msg "Error msg "Object variable or With block variable not set". I cannot figure why.

The code comes from excel vba- extract text between 2 characters, so it should be easy, right? Well, not for me!

I have fiddled with it for several hours, without results.

   Sub FindStrings()

   Dim sheet2 As Worksheet
   Dim i As Integer
   Dim openPos As Long
   Dim clsPos As Long
   Dim textBetween As String
   openPos = 0

   'Using for loop to find the i th occurrence of at '@' for openPos
   For i = 1 To 8

   'get position of start of string
    openPos = InStr(openPos + i, sheet2.Range("H8"), "@", vbTextCompare)     

    'Error msg "Object variable or With block variable not set

    'get position of end of string
    clsPos = InStr(openPos + 1 + i, sheet2.Range("H8"), "@", 
    vbTextCompare)  'End of string

   'get the mid string value between openPos and clsPos
   '
    textBetween = Mid(sheet2.Range("H8").Value, openPos + 1, clsPos - 
    openPos - 1)
    MsgBox ("textBetween  " & "i" & textBetween)

   'write to sheet
    sheet2.Cells(7 + i, 8).Value = textBetween

    Next i

    End Sub

I expect to write the strings to the worksheet. The error message is:"Error msg "Object variable or With block variable not set"

You have Dim sheet2 As Worksheet but never assign a worksheet to that variable. — Tim Williams
– Tim Williams, Commented May 23, 2019 at 21:57
You need to set/create the sheet2 object before you can use it. — DDV
– DDV, Commented May 23, 2019 at 21:57
If you just need to split the string on @ then Dim arr: arr = Split(sheet2.Range("H8").Value, "@") will do that. — Tim Williams
– Tim Williams, Commented May 23, 2019 at 22:00

DDV · Accepted Answer · 2019-05-23 22:00:59Z

1

You need to set/create the sheet2 object before you can use it, ie.

Dim sheet2 as Worksheet

Set sheet2 = Sheets("Sheet2")

Alternatively, if you have changed the sheet name reference in the VBE from "Sheet 2" to sheet2, then you no longer need to declare the sheet2 as a worksheet.

answered May 23, 2019 at 22:00

DDV

2,4155 gold badges16 silver badges37 bronze badges

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

Comments

user11547420 · Accepted Answer · 2019-05-23 23:08:36Z

Dim sheet2 As Worksheet
...
sheet2.Range("H8")

You've declared the sheet2 var but never Set it to a worksheet object. By coincidence the second worksheet in the workbook has a Worksheet.Codename property that can be used as an object reference. Referencing sheet2.Range("H8") will work if you intend to reference that worksheet; declaring Dim sheet2 As Worksheet is unnecessary. If your intent is to reference another worksheet, don't use sheet2 as there may be confusion between the second worksheet's codename and the declared variable representing a set object. You will also have to Set the var to a worksheet object.

'write to sheet
 sheet2.Cells(7 + i, 8).Value = textBetween

The above writes textBetween to sheet2.Range("H8") during the first iteration of the For ... Next loop. Subsequent loops reread the overwritten value so your results are not going to be what you expect.

Your best option is to Split the string into a zero-based array and pick out the piece you want to return. A UserDefined function can be used within a public sub or directly on the worksheet.

Option Explicit

Sub FindStrings()

    Dim i As Long
    Dim textBetween As String

    For i = 1 To 8

        textBetween = FindNthString(Sheet2.Range("H8").Value, i)

        'write to sheet
        Sheet2.Cells(8 + i, "H").Value = textBetween

    Next i

End Sub

Function FindNthString(str As String, ndx As Long, _
                       Optional delim As String = "@")

    FindNthString = CVErr(xlErrNA)

    'Split uses a zero-based array by default
    'the first 'piece' is at position 0
    ndx = ndx - 1

    If UBound(Split(str, delim)) >= ndx And ndx >= 0 Then

        FindNthString = Split(str, delim)(ndx)

    End If

End Function

enter image description here

Collectives™ on Stack Overflow

VBA Extract Strings Between Two Characters

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related