0

I'm having issues getting a find/replace loop to work. I have a list of 122 values I want to easily find/replace, by saving a macro to my personal workbook. I'm trying to simplify it so that the values are each defined with a logical string name, then a find/replace increments up through the strings. Here's what I have:

Sub utf8_cleanup()

' reference: http://www.i18nqa.com/debug/utf8-debug.html

Dim find_prefix As String
Dim replace_prefix As String

Dim find_1 As String
Dim replace_1 As String
Dim find_2 As String
Dim replace_2 As String
Dim find_3 As String
Dim replace_3 As String
Dim replace_count As Integer

find_prefix = "find_"
replace_prefix = "replace_"
find_1 = "­"
replace_1 = "­"
find_2 = "–"
replace_2 = "–"
find_3 = "—"
replace_3 = "—"
' 122 of these pairs
replace_count = 1

Do Until replace_count = 122

find_value = find_prefix & replace_count
replace_value = replace_prefix & replace_count

Debug.Print "Finding " & find_value & "; replacing with " & replace_value

Cells.Replace What:=find_value, Replacement:=replace_value, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False

Debug.Print "Done " & replace_count & " replacement(s)"

replace_count = replace_count + 1

Loop

End Sub

The end result is the macro searching for the text of "find_value" and "replace_value", rather than the values of those strings (for example find_2 should translate to "­", or replace_2 translating to "-"). I'm sure this is something stupidly simple--it's been months since I touched VBA.

Thanks in advance.

Community
  • 1
  • 1
seegoon
  • 563
  • 1
  • 8
  • 15
  • AFAIK what you are trying to do is not possible. See if this helps: http://stackoverflow.com/questions/18311320/excel-vba-using-select-case-but-now-need-an-array/18311783#18311783 – Ioannis Aug 29 '13 at 12:06

1 Answers1

1

You are searching for the name of the variable, not the variable itself. What if you used an array instead?

Sub utf8_cleanup()

' reference: http://www.i18nqa.com/debug/utf8-debug.html

'Dim find_prefix As String
'Dim replace_prefix As String
'
'Dim find_1 As String
'Dim replace_1 As String
'Dim find_2 As String
'Dim replace_2 As String
'Dim find_3 As String
'Dim replace_3 As String
Dim replace_count As Integer

'find_prefix = "find_"
'replace_prefix = "replace_"

'strings to search:
Dim find_bin(122) As String
Dim replace_bin(122) As String

find_bin(1) = "­"
replace_bin(1) = "­"
find_bin(2) = "–"
replace_bin(2) = "–"
find_bin(3) = "—"
replace_bin(3) = "—"
' 122 of these pairs
replace_count = 0

Do Until replace_count = 122 - 1

find_value = find_prefix & replace_count
replace_value = replace_prefix & replace_count

Debug.Print "Finding " & find_bin(replace_count) & _
  "; replacing with " & replace_bin(replace_count)

Cells.Replace What:=find_bin(replace_count), Replacement:=replace_bin(replace_count), _
  LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False

Debug.Print "Done " & replace_count & " replacement(s)"

replace_count = replace_count + 1

Loop

End Sub
Aaron Thomas
  • 5,054
  • 8
  • 43
  • 89
  • 1
    Actually, since an array starts with 0, you should start with find_bin(0), not find_bin(1). Same with replace_bin(). – Aaron Thomas Aug 29 '13 at 12:07
  • This is super old now--but to confirm, this worked perfectly. I don't fully understand how, though. Can you provide any explanation into what's at work here? Thank you! – seegoon Nov 15 '13 at 16:15
  • 1
    @seegoon Well, you have many values you're searching for to replace. If the values are all of the same type (in this case, a string), you can use an array. This provides an easier way to reference, and it's often easier to program, especially if you're looping through each value in the array. That's what my suggested solution was built on. In addition, your code had an error in it: it was not searching for the value, it was searching for the variable name assigned to each value. Hope this points you in the right direction, let me know if there's anything else. – Aaron Thomas Nov 19 '13 at 12:57