OssieMac

In your example
'strInitial = "sample [A1] and [B1] and [C1] and [D1]"
how can I get lets say a MsgBox showing the actual text from the TextBox
but replacing the columns (cells) references with the actual value of the
cell referenced?

i.e
"sample TODAY and 100 and TOMORROW and 200 instead of
"sample [A1] and [B1] and [C1] and [D1]


"OssieMac" wrote:

Hi Luis,

By the time I finished doing some work and testing Joel had already posted
an answer. However, I'll also post my answer because I interpreted your
question as 'extract several variables from text box content' and Joel's
answer only extracts one. If it is only one then I suggest you use Joel's
code.

Dimension the array large enough to accommodate the maximum extractions.

Sub TextBox_Test()
Dim myArray(10)
Dim arrayElement As Integer
Dim strInitial As String
Dim strBracket1 As Integer
Dim strBracket2 As Integer
Dim searchStart As Integer
Dim strRange As String

searchStart = 1
arrayElement = 0
strInitial = "sample [A] and [b] and [C] and [D]"

'strInitial = TextBox1.Value

Do
'Find opening bracket (after previous find if looped)
strBracket1 = InStr(searchStart, strInitial, "[")
If strBracket1 0 Then
strBracket1 = strBracket1 + 1
Else
'No more to find
strBracket1 = 0 'Probabley not needed
Exit Do
End If

'Find closing bracket starting after opening bracket
strBracket2 = InStr(strBracket1, strInitial, "]")

'Assign to an array element
myArray(arrayElement) = Mid(strInitial, strBracket1, strBracket2 -
strBracket1)

'Reset serchStart to search after last open bracket find
searchStart = strBracket1
arrayElement = arrayElement + 1

Loop While strBracket1 0

'For testing only
For i = 0 To UBound(myArray)
If myArray(i) 0 Then
MsgBox myArray(i)
Else
i = UBound(myArray)
End If
Next i

'Example of using the array values to address a range
'Concatenate the array values with the columns values
strRange = myArray(0) & "2"
Sheets("Sheet1").Range(strRange) = "Test"


End Sub


Regards,

OssieMac



"LuisE" wrote:

I have a textbox in an user form. The user is to type a paragraph in the
textbox.
I need to extract from that text any letter between brackets as a variable
in order to later refer to the column
i.e whe it finds a [A] it'll refer to cells (A,1)
Thanks