ravl13
02-11-2015, 09:44 AM
Hello,
I'm working on an excel macro to essentially switch the ordering of the city and state in a column, and also change the delimiter character.
Below, I have screenshots illustrating what I want the macro to do:
http://i.imgur.com/NbM4l8l.png
I have the majority of the Macro planned out, except for the hard part of actually identifying and storing the city and state values in the column B cells. In the below code, could someone enlighten me on how to do that properly in VBA? The part of interest is under the comment section that has a bunch of star (*) characters.
'Code for the SwitchOrder Excel Macro
Sub SwitchOrder()
'Specify which sheet we are working with in the workbook
Sheets("Sheet1").Select
Dim City As String
Dim State As String
City = ""
State = ""
'Select the first entry of the "B" column, which contains the city and state, separated by a comma.
Range("B1").Select
'This following loop needs to swap the ordering of the city and state in the "B" column, and then the separator value needs to be a dash instead of a comma.
'I know the State value will always be 2 characters
'The Do Until condition signifies the end of data
Do Until Selection.Value = "" And Selection.Offset(0, -1).Value = ""
'Here, I need code to capture the City and State values in the selected cell
'************************************************************************** **************************
'/*Pseudocode for the following two lines of code - How do I do the below two lines properly in VBA?*/
'************************************************************************** **************************
City = EverythingToTheLeftOfTheCommaInSelectedCell
State = TheTwoCharactersToTheRightOfTheCommaMinusThePrecedingSpaceCharacterInSelect edCell
'Change the cell value to desired format
Selection.Value = State & " - " & City
'Reset variables
State = ""
City = ""
'Move the currently selected cell down one row
Selection.Offset(1, 0).Select
Loop
End Sub
Thank you for taking the time to look through my post!
-ravl13
I'm working on an excel macro to essentially switch the ordering of the city and state in a column, and also change the delimiter character.
Below, I have screenshots illustrating what I want the macro to do:
http://i.imgur.com/NbM4l8l.png
I have the majority of the Macro planned out, except for the hard part of actually identifying and storing the city and state values in the column B cells. In the below code, could someone enlighten me on how to do that properly in VBA? The part of interest is under the comment section that has a bunch of star (*) characters.
'Code for the SwitchOrder Excel Macro
Sub SwitchOrder()
'Specify which sheet we are working with in the workbook
Sheets("Sheet1").Select
Dim City As String
Dim State As String
City = ""
State = ""
'Select the first entry of the "B" column, which contains the city and state, separated by a comma.
Range("B1").Select
'This following loop needs to swap the ordering of the city and state in the "B" column, and then the separator value needs to be a dash instead of a comma.
'I know the State value will always be 2 characters
'The Do Until condition signifies the end of data
Do Until Selection.Value = "" And Selection.Offset(0, -1).Value = ""
'Here, I need code to capture the City and State values in the selected cell
'************************************************************************** **************************
'/*Pseudocode for the following two lines of code - How do I do the below two lines properly in VBA?*/
'************************************************************************** **************************
City = EverythingToTheLeftOfTheCommaInSelectedCell
State = TheTwoCharactersToTheRightOfTheCommaMinusThePrecedingSpaceCharacterInSelect edCell
'Change the cell value to desired format
Selection.Value = State & " - " & City
'Reset variables
State = ""
City = ""
'Move the currently selected cell down one row
Selection.Offset(1, 0).Select
Loop
End Sub
Thank you for taking the time to look through my post!
-ravl13