leal72
08-19-2014, 12:24 PM
I've been putting together this code to properly sequence my data points. The code below does what I need it to do but it's a little slow, just over 1 minute to run through 300 rows of data. Columns A through D contain coordinates for and object and each row is one object. I need the code to find the closest point to the last point. Here's and example of how I need the coordinates to sequence. What should I try to hopefully reduce the amount of time it takes to run through the data?
12142
Sub Optimize()
Dim xVal As Double
Dim yVal As Double
Dim zVal As Double
Dim rVal As Double
Dim x As Long
Dim lastRow As Long
Dim endRange As Long
Dim rIndex As Long
Application.ScreenUpdating = False
lastRow = Sheets("90s_06").Range("Z1").End(xlDown).Row
endRange = Sheets("90s_06").Range("C" & lastRow).End(xlUp).Row
xVal = Sheets("90s_06").Range("A2").Value
yVal = Sheets("90s_06").Range("B2").Value
zVal = Sheets("90s_06").Range("C2").Value
rVal = Sheets("90s_06").Range("D2").Value
For x = 1 To endRange
Sheets("optimized").Range("A" & x).Value = xVal
Sheets("optimized").Range("B" & x).Value = yVal
Sheets("optimized").Range("C" & x).Value = zVal
Sheets("optimized").Range("D" & x).Value = rVal
rIndex = Sheets("90s_06").Range("G2").Value
'Sheets("90s_06").Range("H2").FormulaR1C1 = "=INDIRECT(ADDRESS(RC[-1],1,1,0),FALSE)"
Sheets("90s_06").Range("H2").FormulaR1C1 = "=INDEX(C[-7],RC[-1])"
Sheets("90s_06").Range("I2").FormulaR1C1 = "=INDEX(C[-7],RC[-2])"
Sheets("90s_06").Range("J2").FormulaR1C1 = "=INDEX(C[-7],RC[-3])"
Sheets("90s_06").Range("K2").FormulaR1C1 = "=INDEX(C[-7],RC[-4])"
xVal = Sheets("90s_06").Range("H2").Value
yVal = Sheets("90s_06").Range("I2").Value
zVal = Sheets("90s_06").Range("J2").Value
rVal = Sheets("90s_06").Range("K2").Value
Sheets("90s_06").Range("A2:D2").ClearContents
Sheets("90s_06").Range("A1").Value = xVal
Sheets("90s_06").Range("B1").Value = yVal
Sheets("90s_06").Range("C1").Value = zVal
Sheets("90s_06").Range("D1").Value = rVal
Sheets("90s_06").Range("A" & rIndex, "D" & rIndex).ClearContents
Next
End Sub
12142
Sub Optimize()
Dim xVal As Double
Dim yVal As Double
Dim zVal As Double
Dim rVal As Double
Dim x As Long
Dim lastRow As Long
Dim endRange As Long
Dim rIndex As Long
Application.ScreenUpdating = False
lastRow = Sheets("90s_06").Range("Z1").End(xlDown).Row
endRange = Sheets("90s_06").Range("C" & lastRow).End(xlUp).Row
xVal = Sheets("90s_06").Range("A2").Value
yVal = Sheets("90s_06").Range("B2").Value
zVal = Sheets("90s_06").Range("C2").Value
rVal = Sheets("90s_06").Range("D2").Value
For x = 1 To endRange
Sheets("optimized").Range("A" & x).Value = xVal
Sheets("optimized").Range("B" & x).Value = yVal
Sheets("optimized").Range("C" & x).Value = zVal
Sheets("optimized").Range("D" & x).Value = rVal
rIndex = Sheets("90s_06").Range("G2").Value
'Sheets("90s_06").Range("H2").FormulaR1C1 = "=INDIRECT(ADDRESS(RC[-1],1,1,0),FALSE)"
Sheets("90s_06").Range("H2").FormulaR1C1 = "=INDEX(C[-7],RC[-1])"
Sheets("90s_06").Range("I2").FormulaR1C1 = "=INDEX(C[-7],RC[-2])"
Sheets("90s_06").Range("J2").FormulaR1C1 = "=INDEX(C[-7],RC[-3])"
Sheets("90s_06").Range("K2").FormulaR1C1 = "=INDEX(C[-7],RC[-4])"
xVal = Sheets("90s_06").Range("H2").Value
yVal = Sheets("90s_06").Range("I2").Value
zVal = Sheets("90s_06").Range("J2").Value
rVal = Sheets("90s_06").Range("K2").Value
Sheets("90s_06").Range("A2:D2").ClearContents
Sheets("90s_06").Range("A1").Value = xVal
Sheets("90s_06").Range("B1").Value = yVal
Sheets("90s_06").Range("C1").Value = zVal
Sheets("90s_06").Range("D1").Value = rVal
Sheets("90s_06").Range("A" & rIndex, "D" & rIndex).ClearContents
Next
End Sub