CODEWITHSUNDEEP
excelloopsvba
eli-k
eli-k

Reputation: 11360

Looping over a list of strings in excel VBA

I am trying to write a macro that when run in an excel sheet will select a specific column and there will replace all instances of a list of strings with a list of other specific strings. For example, any instance of "string a1" will be replaced with "string a2", any "string b1" to "string b2", etc'.

So after selecting the column I need to run 

Selection.Replace What:="string a1", Replacement:="string a2", LookAt:=xlWhole    
Selection.Replace What:="string b1", Replacement:="string b2", LookAt:=xlWhole
etc'

Now I would like to create a loop to run through the two lists, but I can't figure out what kind of loop can be used for this.
I would appreciate suggestions about how to do this from a list in a (hidden) sheet, or from a list defined in the VBA module. Many thanks!

Upvotes: 2

Views: 6733

Answers (2)

Robert J.
Robert J.

Reputation: 2711

I believe the fastest way how to interact with a large number of cells is by saving them in the memory.

I personally would store all the strings in a list of strings Dim sArray() as String, where afterwards I would simply loop from the first element to the very last like following:

Dim iCounter as Long

For iCounter = lbound(sArray) to UBound(sArray)-1 (based on the update below)
   sArray(iCounter) = '...string manipulation logic here'
Next iCounter

EDIT

I don't quite know how exactly you would like to populate this array, but let's say that your values are in a cells of A1 to the very last cell of column A, then you can do the following 

'initialize initial array
ReDim sArray(0 to 0)

'now loop through the cells
For iCounter = 1 to Range("A999999").End(XlUp).Row 
    If Range("A" & iCounter).value <> vbnullstring then
        'we don't want to store empty strings, right?
        sArray(UBound(sArray)) = Range("A" & iCounter).Value
        ReDim Preserve sArray(0 to UBound(sArray)+1)
    End If       
Next iCounter

Upvotes: 2

Chel
Chel

Reputation: 2623

After putting the pairs into a hidden worksheet, loop through them like this:

Dim r As Range
Set r = [HiddenSheet!A1]
Do Until IsEmpty(r.Value)
    [MainSheet!A:A].Replace r.Value, r.Offset(0, 1).Value, xlWhole
    Set r = r.Offset(1, 0)
Loop

Upvotes: 1

Related Questions