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"
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"
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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.