writing macros in excel

[tunde]

Hi ,
I'm trying to write a macro in excel that'll read the contents in a particular column of a spreadsheet and seperate the contents into a new worksheet. What I mean is,
say the field, column G in the spreadsheet, has the contents as follows: "25 Dolemite Way, Manchester, England"
and I want to wirte a macro that reads this and seperates the contents using the comma as the seperator. In the newly created worksheet "Manchester" and "England" are put into 2 seperate cells/fields.
Help (I made a rather confused effort which I've inc.) Can someone tidy it up or suggest alternatives

Thanks

Code:
Private Sub Command1_Click()

Dim intIndex As Integer
Dim intCommaRef As Integer

' Start of string
intCommaRef = 1

' Loop round to the length of the string.
For intIndex = 1 To Len(Application.Activecell)
' Look for a comma.
If Mid(Application.Activecell, intIndex, 1) = "," Then
' Print out upto the new comma.
Text2.Text = Mid(Text1.Text, intCommaRef, intIndex - intCommaRef)
' Ignore the ", "
intCommaRef = intIndex + 2
End If
Next

' Print out the remainder.
Text3.Text = Mid(Text1.Text, intCommaRef, intIndex - intCommaRef)
End Sub

 

[ide]

The following example steps along on the range a1:a30, and writes the comma-separated strings (in the column A) right from the actual cell.

Dim intIndex As Integer
Dim strMyStr As String
Dim i As Range
' Start of string
intCommaRef = 1

For Each i In ActiveSheet.Range("A1:A30&quot
intIndex = 1
strMyStr = i.Value

While InStr(strMyStr, ",&quot > 0 'find ","
i.Offset(0, intIndex) = Left(strMyStr, InStr(strMyStr, ",&quot - 1)
intIndex = intIndex + 1
strMyStr = Mid(strMyStr, InStr(strMyStr, ",&quot + 1, 5000)
Wend
i.Offset(0, intIndex) = strMyStr
Next i


ide
ps: Tunde from Hungary?
Tzupp!

 

[ide]

an other way: to save the worksheet as text, and after open it in excel as a text-file, and set the separation character (delimiter) to comma.

ide

 

[acron]

Select the relevant cells and execute the followig :

Selection.TextToColumns , DataType:=xlDelimited, Comma:=True

 

[Spad]

I am trying to write a macro that will read the totals form the excel spreadsheet and bring them over to a querying software called BRIO.