Hello - I recently converted to excel 2k7, and am having some slowness issues executing some vba code. I created the macro in 2k7, however it takes probably a minute to run, as opposed to 2k3, which runs almost instantly!? I've pasted the code below:
Sub SortData()
Dim i As Long
Dim j As Integer
Dim k As Integer
Dim contract As Variant
Dim position As Integer
Dim rw As Integer
Dim content As Variant
rw = 1
i = 2
j = 4
k = 2
l = 1
'count rows with data
Do
content = Worksheets("Data").Cells(rw, 1).Value
rw = rw + 1
Loop Until content = Empty
Worksheets("Position").Range("A2:Z60").ClearContents
Application.ScreenUpdating = False
For j = 2 To 18 Step 2
For i = 2 To rw
If Worksheets("Data").Cells(i, 4).Value = Worksheets("Position").Cells(1, j) Then
todaybuy = Worksheets("Data").Cells(i, 8).Value
todaysell = Worksheets("Data").Cells(i, 10).Value
beglong = Worksheets("Data").Cells(i, 12).Value
begshort = Worksheets("Data").Cells(i, 14).Value
Worksheets("Data").Cells(i, 20).Characters(15, 30).Delete
contract = Worksheets("Data").Cells(i, 20).Value & " " & Worksheets("Data").Cells(i, 21).Value
position = todaybuy - todaysell + beglong - begshort
Worksheets("Position").Cells(k, j).Value = contract
Worksheets("Position").Cells(k, j - 1).Value = position
k = k + 1
End If
Next i
k = 2
Next j
Application.ScreenUpdating = True
End Sub
Basically we're taking data from another sheet in the workbook, performing a basic arithmetical calculation, and pasting values into columns in another sheet. *a note, the variable rw is always <200.
I'll post this in the vba section also.
Thjanks!
Bookmarks