Looping over a list of strings in excel VBA

Question

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!

Do you mean that you need to replace all instances of string a in a larger data-set with string b? Or do you have two columns and need to replace all of the entries in the second with the first? Could you provide a clearer example of your data, and maybe some things that you've already tried? — F. Stephen Q
– F. Stephen Q, Commented Jun 21, 2016 at 16:59
@F. Stephen Q I will add and edit with some more information — eli-k
– eli-k, Commented Jun 21, 2016 at 17:20

Robert J. · Accepted Answer · 2016-06-22 06:50:17Z

2

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

edited Jun 22, 2016 at 6:50

answered Jun 21, 2016 at 16:47

Robert J.

2,7218 gold badges36 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

eli-k Over a year ago

could you please add an example of the code that populates the sArray list?

Chel · Accepted Answer · 2016-06-21 19:39:10Z

1

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

answered Jun 21, 2016 at 19:39

Chel

2,6231 gold badge20 silver badges25 bronze badges

Collectives™ on Stack Overflow

Looping over a list of strings in excel VBA

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related