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

[MTMontgomery]

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"

[SamT]

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 The good news is that we can help you do what you want to do.

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?

[Paul_Hossler]

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:

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

[MTMontgomery]

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"

[SamT]

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

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

Note spaces around " South "

[Paul_Hossler]

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

Call ActiveSheet.UsedRange.Replace

with

Call ActiveSheet.Columns(1).Replace

Paul

[MTMontgomery]

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:

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

[SamT]

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.

[p45cal]

try:

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

[MTMontgomery]

That did the trick TY very much