Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
working with strings
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
working with strings
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" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
working with strings
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
working with strings
Do you have Excel 97 by any chance?
-- HTH RP (remove nothere from the email address if mailing direct) "DaSo" wrote in message ... 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
working with strings
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
working with strings
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) "DaSo" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find and replace numeric strings in larger text strings | Excel Worksheet Functions | |||
Working with Strings | Excel Discussion (Misc queries) | |||
How to find number of pairs of strings from list of strings? | Excel Worksheet Functions | |||
Working with strings | Excel Programming | |||
Working with strings | Excel Programming |