By setting Screen Updating to False at the Start of the macro, you will not only stop the constant screen flickering associated with recorded macro, but also greatly speed up the execution of the macro.The reason it speeds up code is because Excel no longer needs to repaint the screen whenever it encounters such commands as Select, Activate, Large Scroll, Small Scroll and many others. Screen Updating=False should be placed at the Start of your macro like shown below Note how we have set the Screen Updating back to True on completion.However, your knowledge of VBA leaves much to be desired.
If you're not already familiar with forums, watch our Welcome Guide to get started.Activate lines when switching between two worksheets. The procedure: Sub Get Share Values() ' ' Reads share prices from FTSE100 sheet for my Portfolio Dim rng Share As Range Dim rng Share Find As Range Dim var Share Value(1 To 3) As Variant Dim str Share Name(1 To 3) As String Dim x As Integer Dim rng As Range Application. Activate 'Goto FTSE sheet 'Look for each share name on the FTSE sheet then get the current quote For x = 1 To 3 Cells.Whenever I set one or both as a range object, the 'Find' line throws up errors, usually that the Find Method is not supported by the object or the object isn't valid. Screen Updating = False Set rng Share = This Workbook. Range("A3") 'Get names of shares into array For x = 1 To 3 str Share Name(x) = rng Share. Find(What:=str Share Name(x), After:=Active Cell, Look In:=xl Formulas, _ Look At:=xl Part, Search Order:=xl By Rows, Search Direction:=xl Next, _ Match Case:=False, Search Format:=False).Activate 'Necessary to split the offsets into two actions as the 'Name' cell 'is merged with the B column Set rng Share Find = Active Cell. Activate 'and put the values into col B of Portfolio sheet For x = 1 To 3 rng Share.Offset(2, 0) 'Down two rows var Share Value(x) = rng Share Find. Offset(x - 1, 1) = var Share Value(x) Next Set rng Share = Nothing Set rng Share Find = Nothing Application.By using explicit object references you always know what object is being addressed, whereever you are in your code and whatever has been selected or has made the selection (unexpectantly...) change.