[SOLVED:] VBA Coding to Repalce a List of Words with one word.

VBA Coding to Repalce a List of Words with one word.

So I have a lot of state broken out into abbreviations like GA, FL, AL, and etc. What I am trying to do is write a code that with will replace them with "South". After looking at many threads I have put these lines together but still not getting the desired affect

Sub FindReplace()
Dim B As Long
For Each B In ActiveSheet.UsedRange
B = "GA" & "FL" & "AL"
B = Replace(a, "B", "South")
Next
End Sub

I keep on getting " For each control variable must be variant or object"

MT,

Hi and Welcome to VBA Express.

In your For Each loop the Control Variable is B, but B is a Long Type variable. ie certain numbers only.

That is the first Error, there are many more. In fact every line between Dim and Next is wrong.

That's the bad news :crying: The good news is that we can help you do what you want to do. :cloud9:

Unfortunately, there is not enough information in your post. Can you go to the Advanced reply page and Attach a sample workbook, (Manage Attachments,) and explain the existing scenario and what you want to accomplish in a little more detail?

There's really no 'Intro to VBA' in the online help, but there's plenty of books available that have a good overview of programming and using VBA to 'drive' Excel.

One way to get you started:

Code:

Option Explicit Sub FindReplace() Dim vAbbr As Variant, vStates As Variant vStates = Array("GA", "FL", "AL") ' etc. For Each vAbbr In vStates Call ActiveSheet.UsedRange.Replace(vAbbr, "South", xlPart, , , vbTextCompare) ' Call ActiveSheet.UsedRange.Replace(vAbbr, "South", xlWhole, , , vbTextCompare) ' entire cell Next vAbbr End Sub

Paul

TY Paul.

So I tried to tweek that code to incorporate all the abbrviations for the states and it looks like the following:

Sub FindReplace()
Dim vAbbr As Variant, vStates As Variant, vNorthE As Variant, vMW As Variant, vWest As Variant

vStates = Array("TN", "FL", "AL", "TX", "NC", "SC", "LA", "KY", "AR", "OK", "VA", "TN", "MS", "DE", "DC", "WV")

vNorthE = Array("NJ", "PA", "NH", "CT", "ME", "MA", "NY")

vMW = Array("IN", "OH", "MD", "MI", "KS", "IL", "WI", "MO", "MN", "NE", "SD", "IA")

vWest = Array("CO", "AZ", "AK", "CA", "OR", "WA", "NV", "NM")

For Each vAbbr In vStates
Call ActiveSheet.UsedRange.Replace(vAbbr, "South", xlPart, , , vbTextCompare)
' Call ActiveSheet.UsedRange.Replace(vAbbr, "South", xlWhole, , , vbTextCompare) ' entire cell
Next vAbbr

For Each vAbbr In vNorthE
Call ActiveSheet.UsedRange.Replace(vAbbr, "North East", xlPart, , , vbTextCompare)
' Call ActiveSheet.UsedRange.Replace(vAbbr, "North East", xlWhole, , , vbTextCompare) ' entire cell
Next vAbbr

For Each vAbbr In vMW
Call ActiveSheet.UsedRange.Replace(vAbbr, "Midwest", xlPart, , , vbTextCompare)
' Call ActiveSheet.UsedRange.Replace(vAbbr, "Midwest", xlWhole, , , vbTextCompare) ' entire cell
Next vAbbr

For Each vAbbr In vWest
Call ActiveSheet.UsedRange.Replace(vAbbr, "West", xlPart, , , vbTextCompare)
' Call ActiveSheet.UsedRange.Replace(vAbbr, "West", xlWhole, , , vbTextCompare) ' entire cell
Next vAbbr

End Sub

It runs but I am getting a slight problem for some of them. Like if one is suppose to say "North East" it comes up as "NWestth East" or if it suppose to say "Midwest" it comes up as "Midwestdwest". How can I stopped that from happening.

Also is their any way to reference the Abbrv like "GA" located in Column (A1) and instead of replace the GA it goes to Column (B1) and reads "South"

Replace(" " & vAbbr & " " & , " South ", xlPart, , , vbTextCompare)

Looks for vAbbr surrounded by spaces. Without the spaces Ogasm becomes OrSouthsm :rofl:

Note spaces around " South "

Quote:

Also is their any way to reference the Abbrv like "GA" located in Column (A1) and instead of replace the GA it goes to Column (B1) and reads "South"

Not exactly sure I understand, but maybe replace

Code:

Call ActiveSheet.UsedRange.Replace

with

Code:

Call ActiveSheet.Columns(1).Replace

Paul

Trying Reference a cell and Input data in another column

What I am trying to do is get the references Abbrv in Column V and put the region in column W.

So for example: What is current happening is that the cells are just replacing the Abbriviations with the region and I know why its doing it.
Column V Column V

GA South

IN Midwest

What I want to happen is to have my coding identify the abbriviation in column V and input the South in column W. I need help with this.

Column V Column W

GA South

IN Midwest

So Here is the coding I am working with:

Code:

Sub FindReplace() Dim vAbbr As Variant, vStates As Variant, vNorthE As Variant, vMW As Variant, vWest As Variant vStates = Array("GA", "FL", "AL", "TX", "NC", "SC", "LA", "KY", "AR", "OK", "VA", "TN", "MS", "DE", "DC", "WV") vNorthE = Array("NJ", "PA", "NH", "CT", "ME", "MA", "NY") vMW = Array("IN", "OH", "MD", "MI", "KS", "IL", "WI", "MO", "MN", "NE", "SD", "IA") vWest = Array("CO", "AZ", "AK", "CA", "OR", "WA", "NV", "NM") For Each vAbbr In vStates Call ActiveSheet.Range("V:V").Replace(vAbbr, "South", vbTextCompare) ' Call ActiveSheet.UsedRange.Replace(vAbbr, "South", xlWhole, , , vbTextCompare) ' entire cell Next vAbbr For Each vAbbr In vNorthE Call ActiveSheet.Range("V:V").Replace(vAbbr, "North East", vbTextCompare) ' Call ActiveSheet.UsedRange.Replace(vAbbr, "South", xlWhole, , , vbTextCompare) ' entire cell Next vAbbr For Each vAbbr In vMW Call ActiveSheet.Range("V:V").Replace(vAbbr, "Midwest", vbTextCompare) ' Call ActiveSheet.UsedRange.Replace(vAbbr, "South", xlWhole, , , vbTextCompare) ' entire cell Next vAbbr For Each vAbbr In vWest Call ActiveSheet.Range("V:V").Replace(vAbbr, "West", vbTextCompare) ' Call ActiveSheet.UsedRange.Replace(vAbbr, "South", xlWhole, , , vbTextCompare) ' entire cell Next vAbbr End Sub

Montgomery. Do not start a new thread with the same question, just because you are dis-satisfied with the responses you are getting from the current thread. Please read the Forum FAQs linked to in my Signature.

Because this thread has some slight additional information, I am merging it with the existing thread and not just deleting it as is customary.

try:

Code:

Sub FindRegion() Dim States(0 To 3), Areas, cll As Range, i As Long States(0) = Array("GA", "FL", "AL", "TX", "NC", "SC", "LA", "KY", "AR", "OK", "VA", "TN", "MS", "DE", "DC", "WV") States(1) = Array("NJ", "PA", "NH", "CT", "ME", "MA", "NY") States(2) = Array("IN", "OH", "MD", "MI", "KS", "IL", "WI", "MO", "MN", "NE", "SD", "IA") States(3) = Array("CO", "AZ", "AK", "CA", "OR", "WA", "NV", "NM") Areas = Array("South", "North East", "Mid-West", "West") For Each cll In Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("V:V")).Cells For i = 0 To 3 If Not IsError(Application.Match(cll.Value, States(i), 0)) Then cll.Offset(, 1) = Areas(i) Exit For End If Next i Next cll End Sub

That did the trick TY very much