Excel 2010 VBA dynamic info to static variable

Question

I have a number of cells with information formatted like this

XXXXXXXXXXXXXXXXXXX_'XXXXXXXXXXXXXXXXXXX'XXXXXXXXXXXXXXXXXXX'X'

Using VBA I want to split the information uing the quote as a delimiter

I take that cell in as a string.

I wrote a loop that goes through the string one character at a time and count the quotes (here it happens to be four but that could change) it stores that count in a variable: numQuotes

I wrote a second loop to go through the string and note the numerical position of the quote. I initialized a dynamic array called positions() and give it a size of numQuotes. Then each time it detects a quote it feeds that quotes position number into the array.

The code looks like this:

   ' loop2
    ' start with a dynamic array
    Dim positions() As Integer, size As Integer, k As Integer
    size = quoteCount
    ReDim positions(size)

    Dim quoteCount2 As Integer
    quoteCount2 = 0

    For k = 1 To stringLength

            Dim CharInspector2 As String
            CharInspector2 = Mid(cellContents, k, 1)
            If CharInspector2 = "'" Then
            quoteCount2 = quoteCount2 + 1
            positions(quoteCount2) = k
            End If
    Next k

That all works! But now I need to get the quote positions out into static variables for the next part of my code.

Is there a way to do that?

Thanks

Aharon

stackoverflow.com/questions/7097684/…

Ravi Yenugu
– Ravi Yenugu

2014-06-25 13:08:21 +00:00
Commented Jun 25, 2014 at 13:08 — Ravi Yenugu
– Ravi Yenugu, Commented Jun 25, 2014 at 13:08
Is there a reason you don't just use Split on the string?

Rory
– Rory

2014-06-25 13:12:42 +00:00
Commented Jun 25, 2014 at 13:12 — Rory
– Rory, Commented Jun 25, 2014 at 13:12

sous2817 · Accepted Answer · 2014-06-25 13:15:03Z

0

Here is one way:

Option Explicit

Sub test()
Dim foundPosition As Integer
Dim startPosition As Integer
Dim testString As String

testString = "XXXXXXXXXXXXXXXXXXX_'XXXXXXXXXXXXXXXXXXX'XXXXXXXXXXXXXXXXXXX'X'"

startPosition = 0

Do
    startPosition = startPosition + 1
    foundPosition = InStr(startPosition, testString, "'")
    If foundPosition <> 0 Then
        ' Do whatever you need to do with that position, here I'm just spitting out the position to the immediate window
        Debug.Print (foundPosition)
    End If
    startPosition = foundPosition
Loop Until startPosition = 0
End Sub

answered Jun 25, 2014 at 13:15

sous2817

3,9602 gold badges37 silver badges37 bronze badges

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

Collectives™ on Stack Overflow

Excel 2010 VBA dynamic info to static variable

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related