Microsoft Office Tutorials and References
In Depth Information
Converting Data to a Tabular Format
'Did the user cancel?¶
If TypeName(sReplace) = "Boolean" Then Exit Sub¶
'Turn off screen updating¶
Application.ScreenUpdating = False¶
'Loop through the workbooks¶
For Each Book In Workbooks¶
For Each WS In Book.Worksheets¶
WS.Cells.Replace What:=CStr(sFind), _¶
Replacement:=CStr(sReplace), LookAt:=xlPart, _¶
SearchOrder:=xlByRows, MatchCase:=False¶
Next WS¶
Next Book¶
'Restore screen updating¶
Application.ScreenUpdating = True¶
End Sub¶
Tip: This macro requires no changing of variables because the variables are changed through an
input box provided to the user.
Converting Data to a Tabular Format
With this macro, you can convert data into a format that helps utilize the built-
in features of Excel.
Example file:
Scenario: Inputting information and analyzing it are two
very different things. What could be a good layout for data
entry might be a bad one for analyzing data. A survey is a
typical example, presented to the participant looking like this:
Figure 30 – Great Data Layout for Data Entry
Search JabSto ::

Custom Search