# Optimize VBA code to calculate results faster • ### 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