Format data in excel spreadsheet using VBA

Question

I have a spreadsheet that is used to import data to a database each week.

The data has to be formatted a particular way for it to import correctly.

I want to use VBA to format the data rather than it be a manual process.

This code changes the data to specific names when the XL workbook is re-opened:

Option Explicit

Private Sub Workbook_Open()

'course name changes
Columns("G:G").Select
    Selection.Replace What:="Course Name", replacement:="New Course Name", _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _ False, ReplaceFormat:=False

I repeat this code for each new name. Is there a more concise way of writing this that uses less text? Possibly a way that lets me list all the course names in an array as key value pairs, this way it would be easier to maintain. Thanks

To answer your question, yes there is a way to store data as key value pairs. You can use a collection. I myself do not have enough practice using them though, so I won't try to write a macro; but I do use this reference frequently. — PartyHatPanda
– PartyHatPanda, Commented Nov 14, 2016 at 22:00
I'm not quite sure what you are asking. Are you just doing lots of find and replaces or is there more to it? — SJR
– SJR, Commented Nov 14, 2016 at 23:49

Hambone · Accepted Answer · 2016-11-15 02:22:52Z

1

Rather than going through a list of find/replace strings, I think it would be better to use a dictionary structure and loop through each of the cells in your range (Column G) once and evaluate if the phrase is in the dictionary.

A find/replace on a column on a list of phrases is going to be very expensive because each search will go through every value in the range. If you have 1,000 phrase substitutions and 1,000,000 rows, you better go get a sandwich because it's going to be a while.

Looping through the range using a collection is better but still not as good as a dictionary. A dictionary search is O(1), compared to a standard collection/list search which can be O(n). This will be very significant for non-matches, where the collection approach would have you go through every item in the collection before discovering there is no match -- a dictionary knows right away if there is a match and if so, what it is.

Here is an example of how that would work on column G:

Sub ReplacePhrases()

  Dim dict As New Dictionary
  Dim row, lastRow As Long
  Dim replace As String

  dict.Add "Course Name", "New Course Name"
  dict.Add "VBA, 2nd Edition", "VBA 3rd Edition"

  lastRow = ActiveWorkbook.ActiveSheet.UsedRange.Rows.Count

  For row = 1 To lastRow
    replace = dict(Cells(row, 7).Value2)
    If replace <> "" Then
      Cells(row, 7).Value2 = replace
    End If
  Next row

End Sub

The dict.Add lines could be replace by iterating through a list of cells in another worksheet, lines from a file, rows from a database or whatever else you deem appropriate.

answered Nov 15, 2016 at 2:22

Hambone

16.5k8 gold badges54 silver badges79 bronze badges

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

1 Comment

Pete Over a year ago

This worked well, a couple of points to add. I needed to have Microsoft Scripting Runtime active to make reference to the dictionary class. To do this go to Tools > References > Microsoft Scripting Runtime and click ok. Also had to include a new instance of the dictionary class before I could add items to it. Did this using Set dict = New Dictionary. The final code looked like: Dim dict As Dictionary Set dict = New Dictionary Dim row, lastRow As Long Dim replace As String The rest of the code was the same as above.

Collectives™ on Stack Overflow

Format data in excel spreadsheet using VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related