CODEWITHSUNDEEP
vbaexcel
Pete
Pete

Reputation: 171

Format data in excel spreadsheet using VBA

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

Upvotes: 1

Views: 1149

Answers (1)

Hambone
Hambone

Reputation: 16377

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.

Upvotes: 1

Related Questions