working with strings

[DaSo]

Hi!
Could anybody explain me how "Split" or something similar works?
I have a string and in a loop I want to choose this string´s characters
according to index(i).
I have a problem with running the following macro:

Sub choose()

Dim myString As String
Dim StringValues
Dim i As Long

sValues = _
"A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12"

StringValues = Split(myString, ",")

For i = 0 To 12

If Cells(i + 1, "A").Value <> StringValues(i) Then
Cells(i +1, "A").EntireRow.Insert
End If

Next i

End Sub

Any help would be great.

Thanks, Dan


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Bob Phillips]

Split takes a string, and 'splits' all of its components into a array. The
string is passed as the first argument, the component is defined by the
split character, the 2nd argument.

But ... you have to pas the correct variable to it for it to work. Your
string is called sValues, but your split function is using myString. Also,
if you string has 12 values, when you loop through the array, by starting at
0, you must stop at 11 not 12. But better still, use the array's Upper
bound.

Sub Choose()
Dim sValues As String
Dim myString As String
Dim StringValues
Dim i As Long

sValues = _
"A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12"

StringValues = Split(sValues, ",")

For i = 0 To UBound(StringValues)

If Cells(i + 1, "A").Value <> StringValues(i) Then
Cells(i + 1, "A").EntireRow.Insert
End If

Next i

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[DaSo]

Hi Bob!

thanks for your tip, however when I run the macro, it gives me an error
message and says there is something wrong with "Split" function. Any
idea what could it be?

I have eventually written this macro and it works fine:

Sub Choose()

Dim i As Long
Dim j As Long

Dim table
Dim tablex
Dim nazov

table = Array("A", "B", "C", "D", "E", "F", "G", "H")
tablex = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11",
"12")

For j = 0 To 7

For i = 0 To 11

If Cells(12 * j + i + 1, "A").Value <> table(j) + tablex(i)
Then
Cells(12 * j + i + 1, "A").EntireRow.Insert
End If

Next i

Next j

End Sub

Regards, Dan

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Bob Phillips]

Do you have Excel 97 by any chance?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[DaSo]

Hi Bob,

yes I have Excel 97, so I guess that´s the problem?

Dan


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Bob Phillips]

Yes, Excel97 doesn't have the Split function. Try this

Option Explicit

Sub Choose()
Dim sValues As String
Dim myString As String
Dim StringValues
Dim i As Long

sValues = _
"A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12"

StringValues = Split(sValues, ",")

For i = 0 To UBound(StringValues)

If Cells(i + 1, "A").Value <> StringValues(i) Then
Cells(i + 1, "A").EntireRow.Insert
End If

Next i

End Sub

#If VBA6 Then
#Else
'-----------------------------------------------------------------
Function Split(Text As String, _
Optional Delimiter As String = ",") As Variant
'-----------------------------------------------------------------
Dim i As Long
Dim sFormula As String
Dim aryEval
Dim aryValues

If Delimiter = vbNullChar Then
Delimiter = Chr(7)
Text = Replace(Text, vbNullChar, Delimiter)
End If
sFormula = "{""" & Application.Substitute(Text, Delimiter, """,""") &
"""}"
aryEval = Evaluate(sFormula)
ReDim aryValues(0 To UBound(aryEval) - 1)
For i = 0 To UBound(aryValues)
aryValues(i) = aryEval(i + 1)
Next

Split = aryValues

End Function
#End If



--

HTH

RP
(remove nothere from the email address if mailing direct)