none
Optimize VBA code to calculate results faster RRS feed

  • Question

  • Hi, friends,

    I am using this code below to calculate results with two arrays of variables x,y  (longitude and latitude) in a matrix. Because the formula is complicated, it takes very long time to get all the results. Can anyone help me optimize the code so that it runs faster? Thank you!

    The code runs between two tabs, VBA Calculation Sheet and Sheet 2.The first array starts at Cell A3 and the second array starts at cell  B1.  


    Sub calculator()
    Worksheets("Sheet2").Select

    Dim long1 As Double
    Dim lat1 As Double


    Application.ScreenUpdating = False
    x = 3
    y = 2
    While IsEmpty(Cells(x, 1)) = False
        
    lat1 = Cells(x, 1).value



    While IsEmpty(Cells(1, y)) = False

    long1 = Cells(1, y).value

    Worksheets("VBA Calculation Sheet").Select
    Cells(28, 7) = lat1
    Cells(29, 7) = long1

    index = Cells(23, 7).value


    Worksheets("Sheet2").Select

    Cells(x, y) = index

    y = y + 1
    Wend

    x = x + 1
    y = 2

    Wend

    End Sub

    Monday, October 30, 2017 8:44 PM

All replies

  • Hi,

    Please share an Excel file as a sample via cloud storage such as OneDrive, Dropbox, etc.

    (creating test data is a time-consuming task for me)

    Regards,


    Ashidacchi

    Monday, October 30, 2017 11:01 PM
  • Worksheets("VBA Calculation Sheet").Select
    Cells(28, 7) = lat1
    Cells(29, 7) = long1

    index = Cells(23, 7).value

    It is obvious that you have some calculations in that sheet, what we also have to consider.

    I agree with Ashidacchi, we need to see your file.

    Andreas.

    Saturday, November 4, 2017 6:22 PM