Assigning values from delimited text to variables

[indimonk]

Hi

I have a cell value like this

0;1;2

I want to assign it to different variables

a=0
b=1
c=2

How to split the values and assign them to variables in vba? Please note that the size of the values will vary.
For ex. 0;10;156

 

[GTO]

The example is of course only valid if the delimited values are all numbers. That said, I think you want Split()...

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>  <br><SPAN style="color:#00007F">Sub</SPAN> test()<br><SPAN style="color:#00007F">Dim</SPAN> ary() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> n <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> dblRes <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Double</SPAN><br>  <br>  ary = Split("0;10;156", ";")<br>  <br>  <SPAN style="color:#00007F">For</SPAN> n = 1 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(ary)<br>    dblRes = dblRes + <SPAN style="color:#00007F">CDbl</SPAN>(ary)<br>  <SPAN style="color:#00007F">Next</SPAN><br>  <br>  MsgBox dblRes<br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

Mark

 

[Peter_SSs]

In this code the numeric values will be held in an array and could be accessed as
Dary(0)
Dary(1)
Dary(2)

Depending on just what you want to do with those values, it may be sufficient to stop at the asterisked line. At that point the values will be stored in
Sary(0)
Sary(1)
Sary(2)

but they will be stored as text values "0", "1" and "2". However, this may still be sufficient for your purpose.

Sub AssignToVariables()
  Dim Sary As Variant, Dary As Variant
  Dim i As Long
  
  Sary = Split(Range("A1").Value, ";")
  '*****************
  ReDim Dary(0 To UBound(Sary)) As Double
  For i = 0 To UBound(Sary)
    Dary(i) = Sary(i)
  Next i
  
End Sub

 

[indimonk]

Thanks GTO and Peter.

When I run it in loop, the first operation is successful. For the second iteration, it throws an error that the Subscript is out of range.

Please help to resolve this.

 

[Peter_SSs]

Please help to resolve this.

Please post the code that you are referring to and explain what you are trying to do with that code. Also, make it clear which line of the code is giving that error.

 

[GTO]

...For n = 1 To UBound(ary)...

ACK!

Also, please note that Peter used the correct lower-bound, as Split() returns a zero-based array. My bad

Mark

 

[indimonk]

Sorry for not being clear with my question.

I have a delimited content in Cell 2,2. For ex: 3;1;2 (size is always three). This has to be split and assigned to different variables to perform few calculations. This action should repeat for subsequent rows.

Here is the code:

Dim  m As Variant, n As Variant, p As Variant, q As Variant[/FONT]
[FONT=arial]Dim ary As String[/FONT]

[FONT=arial]Sheets("Sheet2").Select[/FONT]

[FONT=arial]a = 2[/FONT]
[FONT=arial]b = 3[/FONT]

[FONT=arial]For a = 2 To 10[/FONT]
[FONT=arial]ary = Split([/FONT][FONT=arial]ActiveSheet.Cells(a, b - 1).Value[/FONT][FONT=arial], ";")[/FONT]

[FONT=arial]    m = ary(0)[/FONT]
[FONT=arial]    n = ary(1)[/FONT]
[FONT=arial]    p = ary(2)[/FONT]

[FONT=arial]'....
[/FONT]'...
'... does few calculations
'...
'...

[FONT=arial]Sheet2.Cells(a, b).Value = q[/FONT]

[FONT=arial]Next a
[/FONT]

This works for the first iteration. For the second it throws "Subscript out of range" error pointing to m = ary(0)

 

[Peter_SSs]

This works for the first iteration.

By my reckoning that code should not even work for one iteration. If using ary as a variable to receive the result of a split function, ary should be declared as Variant, not String. As it is written it will not run at all for me "Compile error: expected array" with m=ary(0) highlighted.

For the second it throws "Subscript out of range" error pointing to m = ary(0)

That would indicate that cell B3 on the active sheet is empty.



Just repeating something I pointed out earlier: Although it isn't a problem having them and assigning values to them, you don't need variables m, n and p. Any calculations involving them could use ary(0), ary(1) and ary(2) directly just as well.

 

[indimonk]

No luck

I changed the data type to Variant. When I give individual cell references, say B3 (3,2), it works perfectly well. When I run it in for loop, it does the calculation for the B2 and then for B3 it repeats the error "Subscription out of range" pointing to m = ary(0).

 

[Peter_SSs]

No luck

I changed the data type to Variant. When I give individual cell references, say B3 (3,2), it works perfectly well. When I run it in for loop, it does the calculation for the B2 and then for B3 it repeats the error "Subscription out of range" pointing to m = ary(0).

It is working for me. Let's simplify to start with & try this.

1. Make a fresh workbook and ensure it has a sheet called "Sheet2"
2. In Sheet2 enter the values shown below only.

Excel Workbook
	A	B	C	D	E
1
2		1;3;2.3
3		5;8;9
4		6;4;8
5
Sheet2

3. Run the code below (ie not your old code)

Sub AssignToVariables()
  Dim m As Variant, n As Variant, p As Variant, ary As Variant
  Dim a As Long, b As Long
  
  Sheets("Sheet2").Select
  b = 3
  For a = 2 To 4
    ary = Split(Cells(a, b - 1).Value, ";")
    m = ary(0)
    n = ary(1)
    p = ary(2)
    Cells(a, b).Resize(, 3).Value = Array(m, n, p)
  Next a
End Sub

4. Do you get the results shown below?

Excel Workbook
	A	B	C	D	E
1
2		1;3;2.3	1	3	2.3
3		5;8;9	5	8	9
4		6;4;8	6	4	8
5
Sheet2

5. If not,
a) Do you get the values shown in row 2?
b) Do you get the same error message as before?
c) Anything else you can tell us?

6. If you do get the results shown then obviously something else is going on in your sheet and/or your code.
Perhaps your other code is modifying the value of a or b before the second iteration of the loop?
Unless it is huge, perhaps you could post your whole code?