VBA - Get several values to different Variables that are separated by a specific character

[RuiFlora]

Hi Folks,

I am getting stuck in what i believe could be a simple code.
I have a textbox with values for Contracts separated by a ";" (Ex: 205111; 450325; 12000Rev-1) or (Ex: 14522Rev2; 125001) or (Ex: 195654).

What i would like to do is when i press a button, each Contract that is in between the ";" goes to a different Variable. I started with this code

Public Sub ContractIDPopup()

Dim FinalContractID, Contract1, Contract2, Contract3 As String
Dim countSeparators As Variant

FinalContractID = ActiveSheet.TextBoxes("7Contract ID").text
countSeparators = UBound(Split(FinalContractID, ";"))

Contract1 = Split(FinalContractID, ";")(0)
Contract2 = Split(FinalContractID, ";")(1)

End Sub


This works if i have only one ";". The problem is when i have 3 Contracts. In that case i would need:

- Left of the first ";"
- In between the two ";"
- Right of the second ";"

Is this possible?

EDIT: The contracts Lenght is variable

Cheers

 

[stumac]

You have got most of what you need in the code. One issue is if you have declared 3 variables and then have 4 values then you would be in the same position... the way to handle this is to store the data in an array:

Public Sub ContractIDPopup()
Dim FinalContractID() As String
Dim countSeparators As Variant
FinalContractID = Split(ActiveSheet.TextBoxes("7Contract ID").Text, ";")
countSeparators = UBound(FinalContractID())
 
' do what you want with the data like this
For i = 0 To countSeparators
    MsgBox FinalContractID(i)
Next i
End Sub

Also - for info: 'Dim FinalContractID, Contract1, Contract2, Contract3 As String' does not declare all of these variables as strings, only contract3 in this case is a string, the rest are Variants. 'Dim FinalContractID As String, Contract1 As String, Contract2 As String, Contract3 As String' will declare them all as strings.

 

[RuiFlora]

You have got most of what you need in the code. One issue is if you have declared 3 variables and then have 4 values then you would be in the same position... the way to handle this is to store the data in an array:

Public Sub ContractIDPopup()
Dim FinalContractID() As String
Dim countSeparators As Variant
FinalContractID = Split(ActiveSheet.TextBoxes("7Contract ID").Text, ";")
countSeparators = UBound(FinalContractID())
 
' do what you want with the data like this
For i = 0 To countSeparators
    MsgBox FinalContractID(i)
Next i
End Sub

Also - for info: 'Dim FinalContractID, Contract1, Contract2, Contract3 As String' does not declare all of these variables as strings, only contract3 in this case is a string, the rest are Variants. 'Dim FinalContractID As String, Contract1 As String, Contract2 As String, Contract3 As String' will declare them all as strings.

Thank you so much for your input!
I actually have three Textboxes where i want to store the Contract so anyone can edit them. (This code is mostly to force a naming convention)

I came up with this and it works well although it might not be the most efficient.

Public Sub ContractIDPopup()


Dim FinalContractID As String, Contract1 As String, Contract2 As String, Contract3 As String
Dim countSeparators As Variant

With ContractID
.StartUpPosition = 0
.left = Application.left + (0.5 * Application.width) - (0.5 * .width)
.top = Application.top + (0.5 * Application.height) - (0.5 * .height)
End With

FinalContractID = ActiveSheet.TextBoxes("7Contract ID").text
countSeparators = UBound(Split(FinalContractID, ";"))

If FinalContractID = "" Then
GoTo finish
ElseIf countSeparators = 0 Then
ContractID.Contract1tb.text = FinalContractID
ContractID.Contract2tb.text = ""
ContractID.Contract3tb.text = ""
ElseIf countSeparators = 1 Then
ContractID.Contract1tb.text = Split(FinalContractID, ";")(0)
ContractID.Contract2tb.text = Split(FinalContractID, "; ")(1)
ContractID.Contract3tb.text = ""
ElseIf countSeparators = 2 Then
ContractID.Contract1tb.text = Split(FinalContractID, ";")(0)
ContractID.Contract2tb.text = Split(FinalContractID, "; ")(1)
ContractID.Contract3tb.text = Split(FinalContractID, "; ")(2)
End If

finish:

ContractID.Show

End Sub