Using an If-Then Statement in Excel Macro how do i switch adjacent cells?

Question

I have a spreadsheet with two adjacent columns, the city/town is on the left and the state should be on the adjacent right column. There are several errors on the spreadsheet where the state is listed on the Left column and i need to switch these two adjacent ones.

I have a Macro to switch the two adjacent colums, but need to figure out how to run it using an if-then statement to run it only when the State name occurs in the City/town Colum

My Switch Macro is:

Dim cellVal
cellVal = ActiveCell.Value

ActiveCell.Value = ActiveCell.Offset(0, 1).Value
ActiveCell.Offset(0, 1).Value = cellVal

now need to figure out how and where to enter an If-Then statement for where "South Carolina" occurs and run the above macro.

Im new to writing macros in Excel and any advice would help.

Thanks,

Answer 1

You got the right idea. Here's how I'd going about it. Let's say your city is supposed to be in Column c and the State in Column D.



Sub SwitchState()



Dim CityRange As Range, x As Range, MyState As String



With ActiveSheet

Set StateRange = Intersect(.UsedRange, .Range("C:C"))

End With



For Each x In CityRange



Select Case x.Value

Case "South Carolina", "North Carolina", "Indiana", "Illinois", _

"Montana", "Colorado", "North Dakota", "Maine"



MyState = x.Value

x.Value = x.Offset(0, 1).Value

x.Offset(0, 1).Value = MyState



End Select



Next x



End Sub





First, you'll notice that I didn't select anything. You don't have to select cells to make changes to them. Next to explain is the With...End With Statement. You might notice that I used ActiveSheet after With. Anything that begins with a period in between those two With lines is like adding it onto the end of the word that was placed after with. So that Intersect line has two instances of that



ActiveSheet.UsedRange and ActiveSheet.Range("C:C")



The UsedRange is the range on a worksheet made up by the smallest rectangle of cells that will contain all of your data on the worksheet. Worksheets in Excel since Excel 2007 have grown huge. So it makes sense to limit the data you work with. In this instance I limited it to the UsedRange instead of the whole workbook. The Intersect Function Returns a range where two ranges cross/intersect each other. So I'm using that to return the where column C intersects the used range . So say all of your data is contained in rows 2 thru 27. The Intersect would return C2:C27 as the range.



Next is a For/Next loop which loops through every cell in the range I called CityRange or in our example C2:C27. You'll notice that x is declared as a range. I used x as a range of 1 cell. So as the macro loops through each cell it uses the Select Case statement to figure out if the cell's value is one of those state names I listed. Select Case comes in handier than an If-Then statement in this instance because with Select Case statements you can check if a value (x.Value) matches one of multiple values. You'll notice that after the comma after Illinois I added a space followed by an underscore. In VBA programming for macros the computer treats that next line like it is part of the line above. So Montana, Colorado, etc are all considered as on the same programming line as South Carolina.



So the macro is looping through the range that should only have city names and if it finds a state name in the list, it copies that value to the String variable called MyState. A string variable just means it is text. It then copies the value of the cell to the right of it (column D) and it copies that to cell x, the cell in column C on the same row. Then it uses MyState to set the value of the cell in column D.



Afterwards it moves to the next cell down in the column until it reaches the bottom of the UsedRange for that worksheet and quits.

Answer 2

A macro would be an overkill in your case.



Create 2 additional columns. One for town and the other for state.



Using simple if() rules, you the cells in both new can be populated with the correct description.



Since state is very much restricted, you can create a lookup table. If the description match, then its a state, otherwise it is a town.



Also compared to macro, this method is non-destructive as the original data remains unchanged. This way you can fine tune. Some people may write "SC", "S. Carolina" or double space. You'll need to catch them all.

Answer 3

Surely it would be much easier to click on the first row where the error is, run your macro, then click on the next error, and so on. Creating a macro to identify every state in all its possible formats would take you a week.