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
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