VBA snippet manipulate an array to change behaviour following a –

If I recall correctly the issue comes with a hyphenated name. People-power with a capital for the first letter and a lowercase for the last was the desired format, but the text produced by people came with the variation People-Power. It is simply not a good idea to ask a person to verify this when VBA will so merrily do it for you:


Sub arrayManip(thisCell)



' clear out all data

Erase strArray
txt = ""
strTest = ""

'set default case
lCaseOn = False

' string into an array using a " " separator

strTest = WorksheetFunction.Proper(thisCell)
MsgBox "string Proper is " & strTest

strTest = Replace(strTest, "-", " - ")
strTest = Replace(strTest, "‘", " ‘ ")

strArray = Split(strTest, " ")


' itterate through array looking to format text

For i = LBound(strArray) To UBound(strArray)

If strArray(i) = "-" Then
lCaseOn = True


ElseIf strArray(i) = "‘" Then
lCaseOn = True

End If

If lCaseOn Then
strArray(i) = LCase(strArray(i))
lCaseOn = False

End If


' loop through the array and build up a text string for output to cell

Call cleanTxt(strArray, txt)

MsgBox "output will be " & txt

End Sub

Function cleanTxt(strArray, txt)

' string the array back together and remove the spaces


For i = LBound(strArray) To UBound(strArray)

txt = txt & strArray(i) & " "

Next i

txt = Trim(Replace(txt, " - ", "-"))
txt = Trim(Replace(txt, " ‘ ", "‘"))

MsgBox "the string now looks like this " & txt


End Function

 

Leave a Reply

Your email address will not be published. Required fields are marked *

three + 12 =