gjdavis123
New Member
- Joined
- Nov 29, 2012
- Messages
- 5
Multi Find Replace Keywords with Abbreviation from Range - How to loop through a changing range
This is my first post, so please be patient.
I have a sheet with data in col B. This is the data to be updated. This VBA finds the keywords in col B that match the keywords in col N and replaces the found words in col B with the matching abbreviation. This runs well for a fixed number of rows in col N, currently set to 10. I really need it to be as much as 100 rows yet I prefer it to be dynamic. The method used here scans col B and writes update to col C. It then scans col C writes update to col d. etc... through col L. By the time it gets to col L, all 10 abbreviation have been updated. This causes col creep so I will move cols N and O to a separate sheet which is no problem for me.
It seems the coding is inefficient and limited. Is there a way to change it to avoid column creep and/or make it more dynamic by looping through the changing ranges?
Windows 7, Excel 2013
This code works but is limited to 10 keywords/replacements in columns N and O. Example of current before and after results are below the script.
Before:
<tbody>
</tbody>
After:
<tbody>
</tbody>
Thanks for looking! Jeff
This is my first post, so please be patient.
I have a sheet with data in col B. This is the data to be updated. This VBA finds the keywords in col B that match the keywords in col N and replaces the found words in col B with the matching abbreviation. This runs well for a fixed number of rows in col N, currently set to 10. I really need it to be as much as 100 rows yet I prefer it to be dynamic. The method used here scans col B and writes update to col C. It then scans col C writes update to col d. etc... through col L. By the time it gets to col L, all 10 abbreviation have been updated. This causes col creep so I will move cols N and O to a separate sheet which is no problem for me.
It seems the coding is inefficient and limited. Is there a way to change it to avoid column creep and/or make it more dynamic by looping through the changing ranges?
Windows 7, Excel 2013
This code works but is limited to 10 keywords/replacements in columns N and O. Example of current before and after results are below the script.
Code:
Option Explicit
Sub MultiReplaceEDITrange()
Dim rg As Range, c As Range, d As Range, e As Range, f As Range, g As Range, h As Range, ii As Range, j As Range, k As Range, l As Range, m As Range
Dim vLongName As Variant
Dim vAbbrev As Variant
Dim I As Long
Dim x As Long
Dim cl As Long
Dim lrow As Long
On Error Resume Next
Application.ScreenUpdating = False
'Long names in Col 14 (N), Abbr in next Col (O)
cl = 14
'get last row in Names Col
lrow = Cells(65536, cl).End(xlUp).Row
'setup arrays
ReDim vLongName(lrow)
ReDim vAbbrev(lrow)
'load array1 with names
For x = 1 To lrow
vLongName(x) = Cells(x, cl)
Next x
'load array2 with abbr
For x = 1 To lrow
vAbbrev(x) = Cells(x, cl + 1)
Next x
Set rg = Range("B1", Cells(Rows.Count, "B").End(xlUp))
For Each c In rg
For I = LBound(vLongName) To UBound(vLongName)
If InStr(1, c.Text, vLongName(I), vbTextCompare) > 0 Then
c(1, 2).Value = Replace(c.Text, vLongName(I), vAbbrev(I), Compare:=vbTextCompare)
End If
Next I
Next c
Set rg = Range("C1", Cells(Rows.Count, "C").End(xlUp))
For Each d In rg
For I = LBound(vLongName) To UBound(vLongName)
If InStr(1, d.Text, vLongName(I), vbTextCompare) > 0 Then
d(1, 2).Value = Replace(d.Text, vLongName(I), vAbbrev(I), Compare:=vbTextCompare)
End If
Next I
Next d
Set rg = Range("D1", Cells(Rows.Count, "D").End(xlUp))
For Each e In rg
For I = LBound(vLongName) To UBound(vLongName)
If InStr(1, e.Text, vLongName(I), vbTextCompare) > 0 Then
e(1, 2).Value = Replace(e.Text, vLongName(I), vAbbrev(I), Compare:=vbTextCompare)
End If
Next I
Next e
Set rg = Range("E1", Cells(Rows.Count, "E").End(xlUp))
For Each f In rg
For I = LBound(vLongName) To UBound(vLongName)
If InStr(1, f.Text, vLongName(I), vbTextCompare) > 0 Then
f(1, 2).Value = Replace(f.Text, vLongName(I), vAbbrev(I), Compare:=vbTextCompare)
End If
Next I
Next f
Set rg = Range("F1", Cells(Rows.Count, "F").End(xlUp))
For Each g In rg
For I = LBound(vLongName) To UBound(vLongName)
If InStr(1, g.Text, vLongName(I), vbTextCompare) > 0 Then
g(1, 2).Value = Replace(g.Text, vLongName(I), vAbbrev(I), Compare:=vbTextCompare)
End If
Next I
Next g
Set rg = Range("G1", Cells(Rows.Count, "G").End(xlUp))
For Each h In rg
For I = LBound(vLongName) To UBound(vLongName)
If InStr(1, h.Text, vLongName(I), vbTextCompare) > 0 Then
h(1, 2).Value = Replace(h.Text, vLongName(I), vAbbrev(I), Compare:=vbTextCompare)
End If
Next I
Next h
Set rg = Range("H1", Cells(Rows.Count, "H").End(xlUp))
For Each ii In rg
For I = LBound(vLongName) To UBound(vLongName)
If InStr(1, ii.Text, vLongName(I), vbTextCompare) > 0 Then
ii(1, 2).Value = Replace(ii.Text, vLongName(I), vAbbrev(I), Compare:=vbTextCompare)
End If
Next I
Next ii
Set rg = Range("I1", Cells(Rows.Count, "I").End(xlUp))
For Each j In rg
For I = LBound(vLongName) To UBound(vLongName)
If InStr(1, j.Text, vLongName(I), vbTextCompare) > 0 Then
j(1, 2).Value = Replace(j.Text, vLongName(I), vAbbrev(I), Compare:=vbTextCompare)
End If
Next I
Next j
Set rg = Range("J1", Cells(Rows.Count, "J").End(xlUp))
For Each k In rg
For I = LBound(vLongName) To UBound(vLongName)
If InStr(1, k.Text, vLongName(I), vbTextCompare) > 0 Then
k(1, 2).Value = Replace(k.Text, vLongName(I), vAbbrev(I), Compare:=vbTextCompare)
End If
Next I
Next k
Set rg = Range("K1", Cells(Rows.Count, "K").End(xlUp))
For Each l In rg
For I = LBound(vLongName) To UBound(vLongName)
If InStr(1, l.Text, vLongName(I), vbTextCompare) > 0 Then
l(1, 2).Value = Replace(l.Text, vLongName(I), vAbbrev(I), Compare:=vbTextCompare)
End If
Next I
Next l
Set rg = Range("L1", Cells(Rows.Count, "L").End(xlUp))
For Each m In rg
For I = LBound(vLongName) To UBound(vLongName)
If InStr(1, m.Text, vLongName(I), vbTextCompare) > 0 Then
m(1, 2).Value = Replace(m.Text, vLongName(I), vAbbrev(I), Compare:=vbTextCompare)
End If
Next I
Next m
Application.ScreenUpdating = True
End Sub
Before:
Description(Required) | Description(Required) | Description(Required) | Description(Required) | Description(Required) | Description(Required) | Description(Required) | Description(Required) | Description(Required) | Description(Required) | Description(Required) | Description(Required) | Keyword | Abbrev |
Sport Swimming | sport | sprt | |||||||||||
Gymnastics Speed Skating Cross Country Skiing | swimming | swimng | |||||||||||
Short-Track Speed Skating Diving Cycling | country | contry | |||||||||||
Ski Jumping Nordic Combined Athletics Table Tennis | jumping | jump | |||||||||||
Synchronized Swimming Shooting Rowing Fencing Equestrian | single | sngl | |||||||||||
Gymnastics | synchronized | sync | |||||||||||
Short-Track Speed Skating | gymnastics | gym | |||||||||||
Sport | side | 1S | |||||||||||
Gymnastics | combined | combi | |||||||||||
Short-Track Speed Skating | Hybrid | hybrd | |||||||||||
Ski Jumping | |||||||||||||
Synchronized Swimming | |||||||||||||
Ski Jumping Nordic Combined Athletics Table Tennis | |||||||||||||
Gymnastics | |||||||||||||
Short-Track Speed Skating | |||||||||||||
Ski Jumping | |||||||||||||
Ski Jumping Nordic Combined Athletics Table Tennis | |||||||||||||
Ski Jumping Nordic Combined Athletics Table Tennis | |||||||||||||
Short-Track Speed Skating | |||||||||||||
Sport | |||||||||||||
Gymnastics | |||||||||||||
Short-Track Speed Skating | |||||||||||||
Ski Jumping | |||||||||||||
Synchronized Swimming |
<tbody>
</tbody>
After:
Description(Required) | Description(Required) | Description(Required) | Description(Required) | Description(Required) | Description(Required) | Description(Required) | Description(Required) | Description(Required) | Description(Required) | Description(Required) | Description(Required) | Keyword | Abbrev |
Sport Swimming | Sport swimng | sprt swimng | sprt swimng | sprt swimng | sprt swimng | sprt swimng | sprt swimng | sprt swimng | sprt swimng | sprt swimng | sprt swimng | sport | sprt |
Gymnastics Speed Skating Cross Country Skiing | gym Speed Skating Cross Country Skiing | gym Speed Skating Cross contry Skiing | gym Speed Skating Cross contry Skiing | gym Speed Skating Cross contry Skiing | gym Speed Skating Cross contry Skiing | gym Speed Skating Cross contry Skiing | gym Speed Skating Cross contry Skiing | gym Speed Skating Cross contry Skiing | gym Speed Skating Cross contry Skiing | gym Speed Skating Cross contry Skiing | gym Speed Skating Cross contry Skiing | swimming | swimng |
Short-Track Speed Skating Diving Cycling | Short-Track Speed Skating Diving Cycling | Short-Track Speed Skating Diving Cycling | Short-Track Speed Skating Diving Cycling | Short-Track Speed Skating Diving Cycling | Short-Track Speed Skating Diving Cycling | Short-Track Speed Skating Diving Cycling | Short-Track Speed Skating Diving Cycling | Short-Track Speed Skating Diving Cycling | Short-Track Speed Skating Diving Cycling | Short-Track Speed Skating Diving Cycling | Short-Track Speed Skating Diving Cycling | country | contry |
Ski Jumping Nordic Combined Athletics Table Tennis | Ski Jumping Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | jumping | jump |
Synchronized Swimming Shooting Rowing Fencing Equestrian | sync Swimming Shooting Rowing Fencing Equestrian | sync swimng Shooting Rowing Fencing Equestrian | sync swimng Shooting Rowing Fencing Equestrian | sync swimng Shooting Rowing Fencing Equestrian | sync swimng Shooting Rowing Fencing Equestrian | sync swimng Shooting Rowing Fencing Equestrian | sync swimng Shooting Rowing Fencing Equestrian | sync swimng Shooting Rowing Fencing Equestrian | sync swimng Shooting Rowing Fencing Equestrian | sync swimng Shooting Rowing Fencing Equestrian | sync swimng Shooting Rowing Fencing Equestrian | single | sngl |
Gymnastics | gym | gym | gym | gym | gym | gym | gym | gym | gym | gym | gym | synchronized | sync |
Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | gymnastics | gym |
Sport | sprt | sprt | sprt | sprt | sprt | sprt | sprt | sprt | sprt | sprt | sprt | side | 1S |
Gymnastics | gym | gym | gym | gym | gym | gym | gym | gym | gym | gym | gym | combined | combi |
Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Hybrid | hybrd |
Ski Jumping | Ski jump | Ski jump | Ski jump | Ski jump | Ski jump | Ski jump | Ski jump | Ski jump | Ski jump | Ski jump | Ski jump | ||
Synchronized Swimming | sync Swimming | sync swimng | sync swimng | sync swimng | sync swimng | sync swimng | sync swimng | sync swimng | sync swimng | sync swimng | sync swimng | ||
Ski Jumping Nordic Combined Athletics Table Tennis | Ski Jumping Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | ||
Gymnastics | gym | gym | gym | gym | gym | gym | gym | gym | gym | gym | gym | ||
Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | ||
Ski Jumping | Ski jump | Ski jump | Ski jump | Ski jump | Ski jump | Ski jump | Ski jump | Ski jump | Ski jump | Ski jump | Ski jump | ||
Ski Jumping Nordic Combined Athletics Table Tennis | Ski Jumping Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | ||
Ski Jumping Nordic Combined Athletics Table Tennis | Ski Jumping Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | Ski jump Nordic combi Athletics Table Tennis | ||
Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | ||
Sport | sprt | sprt | sprt | sprt | sprt | sprt | sprt | sprt | sprt | sprt | sprt | ||
Gymnastics | gym | gym | gym | gym | gym | gym | gym | gym | gym | gym | gym | ||
Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | Short-Track Speed Skating | ||
Ski Jumping | Ski jump | Ski jump | Ski jump | Ski jump | Ski jump | Ski jump | Ski jump | Ski jump | Ski jump | Ski jump | Ski jump | ||
Synchronized Swimming | sync Swimming | sync swimng | sync swimng | sync swimng | sync swimng | sync swimng | sync swimng | sync swimng | sync swimng | sync swimng | sync swimng |
<tbody>
</tbody>
Thanks for looking! Jeff