CODEWITHSUNDEEP
vbaexcel
Aus Mazin
Aus Mazin

Reputation: 25

Copy cells formulas VBA

I did a program in VBA to copy the formulas in each cell in a specific column, I have 30501 points and the program is really slow even to calculate 100 points, there is a better way to do so?

Sub Copyformulas()


Dim i As Integer
Dim cell As Range
Dim referenceRange As Range
Dim a As String

a = "$T$30510"
Set range1= ActiveSheet.Range("A1:A30510")
Set myrange = Range("T16:T30510")
i = 16

Do Until Cells(20, 30510)
    With range1
        For Each cell In myrange
            If cell.HasFormula Then
                Cells(i, 35).Value = cell.Address
                Cells(i, 36).Value = "'" & CStr(cell.Formula)
                i = i + 1
            End If
        Next
    End With
Loop
End Sub

Upvotes: 0

Views: 145

Answers (2)

Brian
Brian

Reputation: 2108

Try adding the following:

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False

... Your Code ...

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True

You may only need the first one, but they are all good practice in using. Also, where are you using the With ... End With statement? I don't see any use of it in the block.

It is good practice to use Option Explicit at the top of the module. And range1 and myrange are not declared.

Application.Calculation

When a worksheet is accessed or a range's precedents has changed, Excel will automatically recalculate the formulas on the worksheet. Since you are looping over 30,000 times, this causes Excel to recalculate each time through the loop and, thus, slows down performance. 

Application.ScreenUpdating

This line stops Excel from screen flashes and other things that occur as the macro runs.

Application.EnableEvents

This line turns off events, such as Worksheet_Change, so that the event is not triggered. If it is not turned off then any time a change occurs on the worksheet the code in the change event will run. If you have a Worksheet_SelectionChange event then code will run every time you select a different cell. These events are written in the worksheet or workbook objects located in the project window of the VBE and there are many events to choose from. Here is a very simple illustration. Place the following in the Sheet1 object in the project window:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    MsgBox "Hi!"
End Sub

Now click around on the worksheet. You see it responds to each selection change. Now place the following in a regular module:

Sub TestEnableEvents()

Application.EnableEvents = False
ActiveCell.Offset(1, 0).Select
Application.EnableEvents = True

End Sub

When you run the above code the message box will not be triggered.

Upvotes: 0

user6432984
user6432984

Reputation:

You can use SpecialCells to refine your range. You don't need to use ActiveSheet it is implied.

Set rSource = Range("A16:A30510").SpecialCells(xlCellTypeFormulas)

Sub Copyformulas()
    Application.Calculation = xlManual
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    Dim c As Range
    Dim rSource As Range

    Set rSource = ActiveSheet.Range("A16:A30510").SpecialCells(xlCellTypeFormulas)

    For Each c In rSource
        c.Offset(0, 34) = c.Address
        c.Offset(0, 35) = "'" & c.Formula
    Next

    Application.Calculation = xlAutomatic
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

Upvotes: 1

Related Questions