PDA

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



MTMontgomery
09-24-2013, 11:21 AM
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
09-24-2013, 04:39 PM
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?

Paul_Hossler
09-24-2013, 05:57 PM
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
09-25-2013, 10:32 AM
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
09-25-2013, 02:45 PM
Replace(" " & vAbbr & " " & , " South ", xlPart, , , vbTextCompare)

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

Note spaces around " South "

Paul_Hossler
09-25-2013, 07:02 PM
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
09-26-2013, 05:34 AM
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
09-26-2013, 06:50 AM
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
09-26-2013, 08:09 AM
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
09-26-2013, 08:20 AM
That did the trick TY very much