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

Sub test()
'string for testing
textstring = "azf[123]asdfasdf"
'line below is what should be in the final code
'textstring = userform1.TextBox.Text

bracketpos = InStr(textstring, "[")
If bracketpos 0 Then
textstring = Mid(textstring, bracketpos + 1)
bracketpos = InStr(textstring, "]")
If bracketpos 0 Then
textstring = Left(textstring, bracketpos - 1)
End If
End If


End Sub
"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

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

You got it.Thanks a lot
"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

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

Hi Luis,

Firstly I hope that I interpreted your initial post correctly. Your initial
sample only had the column identifer and no row identifier. I added the row
identifier in the second last line of my previous code shown below because I
thought that was what you wanted to do:-

strRange = myArray(0) & "2"

However, the following can be used to return the original text with the
replaced text from the identified cells based upon my original interpretation
whereby I concatenate the row number with the column identifier.

'Add these 2 dim statements to top of macro
Dim strToReplace As String
Dim strReplacement As String

'Insert this code in lieu of where I had "For testing only"
For i = 0 To UBound(myArray)
If myArray(i) 0 Then
'Concatenate the array values with the column values
'to create a range for the replacement string
strRange = myArray(i) & "1"

'Create string to be replaced from array value
strToReplace = "[" & myArray(i) & "]"

'Create the replacement string
strReplacement = Range(strRange)

'Replace existing string with replacment string
strInitial = Replace(strInitial, strToReplace, _
strReplacement, 1, 1)
Else
i = UBound(myArray)
End If
Next i

'MsgBox displays the new string
MsgBox strInitial

Regards,

OssieMac

Ossie

Thanks again for your assistance, it is greatly appreciated.
You interpreted my initial post just fine. I'm trying to force myself to
think and develop the code by myself. But when I feel useless I come here.

My original intent was to get the user to enter a message in a textbox, name
the columns containing the variables using the brackets. Then loop thru those
columns and as many rows in a dynamic range to create a customized message
for each row
i.e.

TextBox1.Value= Dear [A], you owe me [b] dollars, pay me by [C]
A1= KARL B1=5 C1= 10/20/07
A2=TOW B2=3 C2= 10/25/07
and so on
I want to loop thru as many Columns are indicated in the textbox (always can
vary) and as many rows in the range.

I spent a good hour this afternoon and came up with the Replace routine
myself but my version would replace a column at the time for each row.
i.e
Dear KARL, you owe me [b] dollars, pay me by [C]
Dear [A], you owe me [5] dollars, pay me by [C]
and so on

Thanks again for your time and help. Any good reference to studt arrays?

OssieMac

I created an external loop for the rows but it doesn't reset "strInitial" it
just shows the first one as many times as rows in the loop. Any suggestions?

'For testing only
For N = 2 To 3 'rows
For i = 0 To UBound(myArray)
If myArray(i) 0 Then
'Concatenate array values with column values to create a range for the
replacement string
strToReplace = "[" & myArray(i) & "]" 'Create string to be replaced from
array value
strRange = myArray(i) & N 'column and row
strReplacement = Range(strRange) 'Create the replacement string
'Replace existing string with replacment string
strInitial = Replace(strInitial, strToReplace, strReplacement, 1, 1) 'not
looping thru row
Else
i = UBound(myArray)
End If
Next i
MsgBox strInitial 'MsgBox displays the new string
Next N