VBA lesson 5 what does my recorded code mean

When I am recording macro code I am getting a lot of repetitve code, I was hoping there was a way to streamline it to make it more readable.

I get a lot of code like this.
Code:

ActiveWindow.SmallScroll Down:=36
    ActiveWindow.ScrollRow = 48
    ActiveWindow.ScrollRow = 49
    ActiveWindow.ScrollRow = 50
    ActiveWindow.ScrollRow = 51
    ActiveWindow.ScrollRow = 52
    ActiveWindow.ScrollRow = 53
    ActiveWindow.ScrollRow = 55
    ActiveWindow.ScrollRow = 57
    ActiveWindow.ScrollRow = 59
    ActiveWindow.ScrollRow = 62
    ActiveWindow.ScrollRow = 64
    ActiveWindow.ScrollRow = 66
    ActiveWindow.ScrollRow = 69
    ActiveWindow.ScrollRow = 71
    ActiveWindow.ScrollRow = 73
    ActiveWindow.ScrollRow = 74
    ActiveWindow.ScrollRow = 76
    ActiveWindow.ScrollRow = 79
    ActiveWindow.ScrollRow = 81
    ActiveWindow.ScrollRow = 82
    ActiveWindow.ScrollRow = 83
    ActiveWindow.ScrollRow = 84
    ActiveWindow.ScrollRow = 86
    ActiveWindow.ScrollRow = 88
    ActiveWindow.ScrollRow = 89
    ActiveWindow.ScrollRow = 90
    ActiveWindow.ScrollRow = 91
    ActiveWindow.ScrollRow = 92
    ActiveWindow.ScrollRow = 93
    ActiveWindow.ScrollRow = 94
    ActiveWindow.ScrollRow = 96
    ActiveWindow.ScrollRow = 97
    ActiveWindow.ScrollRow = 98
    ActiveWindow.ScrollRow = 99
    ActiveWindow.ScrollRow = 100
    ActiveWindow.ScrollRow = 101
    ActiveWindow.ScrollRow = 102
    ActiveWindow.ScrollRow = 103
    ActiveWindow.ScrollRow = 104
    ActiveWindow.ScrollRow = 105
    ActiveWindow.ScrollRow = 106
    ActiveWindow.ScrollRow = 107
    ActiveWindow.ScrollRow = 108
    ActiveWindow.ScrollRow = 109
    ActiveWindow.ScrollRow = 110
    ActiveWindow.ScrollRow = 111
    ActiveWindow.ScrollRow = 112
    ActiveWindow.ScrollRow = 113
    ActiveWindow.ScrollRow = 114
    ActiveWindow.ScrollRow = 115
    ActiveWindow.ScrollRow = 116
    ActiveWindow.ScrollRow = 117
    ActiveWindow.ScrollRow = 118
    ActiveWindow.ScrollRow = 119
    ActiveWindow.ScrollRow = 120
    ActiveWindow.ScrollRow = 121
    ActiveWindow.ScrollRow = 122
    ActiveWindow.ScrollRow = 123
    ActiveWindow.ScrollRow = 124
    ActiveWindow.ScrollRow = 125
    ActiveWindow.ScrollRow = 126
    ActiveWindow.ScrollRow = 127
    ActiveWindow.ScrollRow = 128
    ActiveWindow.ScrollRow = 129
    ActiveWindow.ScrollRow = 130
    ActiveWindow.ScrollRow = 131
    ActiveWindow.ScrollRow = 132
    ActiveWindow.ScrollRow = 133
    ActiveWindow.ScrollRow = 134
    ActiveWindow.ScrollRow = 136
    ActiveWindow.ScrollRow = 137
    ActiveWindow.ScrollRow = 138
    ActiveWindow.ScrollRow = 139
    ActiveWindow.ScrollRow = 140
    ActiveWindow.ScrollRow = 141
    ActiveWindow.ScrollRow = 142
    ActiveWindow.ScrollRow = 143
    ActiveWindow.ScrollRow = 144
    ActiveWindow.ScrollRow = 145
    ActiveWindow.ScrollRow = 146
    ActiveWindow.ScrollRow = 147
    ActiveWindow.ScrollRow = 148
    ActiveWindow.SmallScroll Down:=2

Hello Again,

I am trying my best to learn VBA.
Someone suggested I start with only one bit of recorded code and then try and re-write it in a simpler less taxing way.

ok so below is simply data where i inserted 6 rows at the top, highlighted the 7th row and applied formating to it by using the format dialog box.

Can someone explain to me how i would make what is below simpler, I would appreciate it.

Rows("1:6").Select
Selection.Insert Shift:=xlDown
Range("A7").Select
Range(Selection, Selection.End(xlToRight)).Select
With Selection.Font
.Name = "Bookman Old Style"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End Sub

i have created a recorded macro that bassically when clicked adds one to the cell eg if value is 10 and the button was clicked then it would turn to 11

the problem is that i want it to go down each time it is clicked therfore
when you click it would first go to A49 and then the second time it should go down to A50 therfore each time it is click the cells goes up by 1 eg A49=1 A50=2 etc
the code is below:

Code:

Sub add1year()
'
' add1year Macro
' Macro recorded 16/01/2008 by
'

'
    Range("A47").Select
    ActiveCell.FormulaR1C1 = "=R[2]C+1"
    Range("A47").Select
    Selection.Copy
    Range("A49").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A47").Select
    Application.CutCopyMode = False
    Selection.ClearContents
End Sub

thanks in advance

In many posts I have found that solution recommended is to work with small codes. I am a non technical person and hence jumble as to where these codes are to be written. Even if they are just to be copu + paste, where do i take them and how to execute them. E.g in one of the recent post http://excelforum.com/showthread.php?t=499280 for inserting rows, a small code was recommended.

How do i take it further ?

thanks in advance...

This is probably easy for most of you guys.

I have the following code (a part of it):

For n = 7 To Worksheets("Input").Range("A1").Value + 4

Sheet2.Cells(n + 2, 4) = "=IF(MAX(R7C6:R[-1]C[2])

I right clicked on a column to insert a column to its left.

The code recorded was

Code:

Sub Macro()

Selection.Insert shift:=x1ToRight

End Sub

If I modify x1ToRight to x1ToLeft, I was hoping to insert a column to the right shifting the active column to the left. But it does not.

How do I modify this code that it inserts a new column to the right of the active column

I am attempting to clean up some recorded code. I used the recorder b/c the macro involves importing data from an Access table, and I was a little unsure as to how to proceed. My issue now is converting a hardcoded value to an Excel value. I managed to do it for one macro which grabs data according to one variable ("CIS") and hoped to be able to have users try another variable ("TaxNbr") if that wasn't available. It became clear that I couldn't run both macros on the same page and paste into the same output area, so I set up another worsheet for the "TaxNbr" variable. Now one macro works and the other doesn't! Help! The code is below (working code first and then problem code (variable assignment in red ):

Code:

Sub Rel_CIS()
'
' Rel_CIS Macro
' Macro recorded 8/15/2008 by Brian Cullen
'
'
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=I:\Financial Analysis & Risk Management\SVA Files\CBT\CalBank\SVA\200807\sva0807.mdb;DefaultDir=I:\F" _
), Array( _
"inancial Analysis & Risk Management\SVA Files\CBT\CalBank\SVA\200807;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5" _
), Array(";")), Destination:=Range("A9:A59"))
.CommandText = Array( _
"SELECT tPSVA.PrcsDate, tPSVA.CIS, tPSVA.TaxNbr, tPSVA.Branch, tPSVA.Cat1, tPSVA.Cat2, tPSVA.AcctID, tPSVA.NoteNbr, tPSVA.ShortName, tPSVA.OffNbr, tPSVA.Bal, tPSVA.Rate, tPSVA.Xfer, tPSVA.BaR, tPSVA.Ba" _
, _
"X, tPSVA.Cap, tPSVA.ALLL, tPSVA.NOPAT, tPSVA.CAPCHG, tPSVA.SVA, tPSVA.OpenDate" & Chr(13) & "" & Chr(10) & "FROM tPSVA tPSVA" & Chr(13) & "" & Chr(10) & " WHERE (tPSVA.CIS = '" & Range("A4").Value & "')" _ 
)
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub
_______________________________________________________________
 
Sub Rel_Tax()
'
' Rel_Tax Macro
' Macro recorded 8/15/2008 by Brian Cullen
'
'
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=I:\Financial Analysis & Risk Management\SVA Files\CBT\CalBank\SVA\200807\sva0807.mdb;DefaultDir=I:\F" _
), Array( _
"inancial Analysis & Risk Management\SVA Files\CBT\CalBank\SVA\200807;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5" _
), Array(";")), Destination:=Range("A9:A59"))
.CommandText = Array( _
"SELECT tPSVA.PrcsDate, tPSVA.CIS, tPSVA.TaxNbr, tPSVA.Branch, tPSVA.Cat1, tPSVA.Cat2, tPSVA.AcctID, tPSVA.NoteNbr, tPSVA.ShortName, tPSVA.OffNbr, tPSVA.Bal, tPSVA.Rate, tPSVA.Xfer, tPSVA.BaR, tPSVA.Ba" _
, _
"X, tPSVA.Cap, tPSVA.ALLL, tPSVA.NOPAT, tPSVA.CAPCHG, tPSVA.SVA, tPSVA.OpenDate" & Chr(13) & "" & Chr(10) & "FROM tPSVA tPSVA" & Chr(13) & "" & Chr(10) & " WHERE (tPSVA.TaxNbr = '" & Range("A4").Value & "')" _ 
)
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

________________________________________________________________

Hi All,

Apologies if this has already been answered, but I recorded a macro at work; called SortStats, which is saved in my PERSONAL.XLS file (Module10).

I wanted to take a copy of this file home, so copied the entire code, and pasted it into the worksheet to which it related in the copied file (for home); however, although it runs perfectly on the PC at work - the copied file with copied macro won't run.

Could anyone help me as to why this might be?

Code as follows:


Code:

Sub SortStats()
'
' SortStats Macro
'

'
    Columns("A:A").Select
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "=TRIM(RC[-5])"
    Range("F2").Select
    Selection.AutoFill Destination:=Range("F2:F367")
    Range("F2:F367").Select
    Selection.Copy
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("C:D").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Player"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Team"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Score"
    Columns("D:D").Select
    Selection.Delete Shift:=xlToLeft
    Range("D2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-3]=""A Johnson"",RC[-2]=""Fulham""),""A Johnson (Ful)"",IF(AND(RC[-3]=""A Johnson"",RC[-2]=""Man City""),""A Johnson (Man C)"",RC[-3]))"
    Range("D3").Select
    ActiveWindow.SmallScroll Down:=-15
    Range("D2").Select
    Selection.AutoFill Destination:=Range("D2:D367")
    Range("D2:D367").Select
    ActiveWindow.SmallScroll Down:=120
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-156
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("D:D").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Columns("A:A").EntireColumn.AutoFit
    Columns("B:B").EntireColumn.AutoFit
    Columns("C:C").EntireColumn.AutoFit
    Columns("C:C").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 1
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("C2").Select
    Columns("C:C").ColumnWidth = 10.86
    Columns("B:B").ColumnWidth = 15.57
    Columns("A:A").ColumnWidth = 24.29
    Range("A2").Select
    ActiveWindow.FreezePanes = True
    ActiveWindow.SmallScroll Down:=-33
    Range("D2").Select
End Sub

Many thanks,


Andy

Good Evening to everyone..

I have recorded a macro code...but it is taking lot of time for getting the output..Can anyone help me to tweak my code..by cleaning the unnecessary lines to execute the code more quickly

Code:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 11/04/2011 by ABS
'
' Keyboard Shortcut: Ctrl+a
'
    ChDir "E:\Macros\Input"
    Workbooks.Open Filename:="E:\Macros\Input\c.csv"
    Selection.AutoFilter
    Selection.AutoFilter Field:=2, Criteria1:="=ET", Operator:=xlOr, _
        Criteria2:="=BT"
    Range("B:B,G:G,H:H,J:J").Select
    Range("J1").Activate
    Selection.AutoFilter Field:=2
    Selection.Delete Shift:=xlToLeft
    Columns("G:G").Select
    Selection.Cut
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight
    Selection.NumberFormat = "yyyymmdd"
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;E:\Macros\Input\m.DAT", Destination:=Range("A1"))
        .Name = "MTO_04012010"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Range("A:A,B:B,E:E,G:G").Select
    Range("G1").Activate
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=2, Criteria1:="=ET", Operator:=xlOr, _
        Criteria2:="=BE"
    Sheets("c").Select
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-7],Sheet1!C[-7]:C[-5],3,0)"
    Range("H1").Select
    Selection.AutoFill Destination:=Range("H1:H1333")
    Range("H1:H1333").Select
    Range("H1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Replace What:="#N/A", Replacement:="0", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    ChDir "E:\Macros\Output"
    ActiveWorkbook.SaveAs Filename:="E:\Macros\Output\c.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
        Application.DisplayAlerts = False
    ActiveWindow.Close
    ActiveWindow.Close
End Sub

Thank u

The following code formats cells in the current selection to have borders around each cell.

Sub MakeBorders()
' MakeBorders around the SAP output

Sheets("SAP_SMTool").Activate
With ActiveCell
.CurrentRegion.Select
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
End With

With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub


The code works fine but there is a lot of repetition in the code for each of the sides of the cells. What options are there to compact the code with "with - with end", looping or some other means.

Regards David