vba code running slowly.

[greg.begin]

I have written a code to open a workbook on a network and calculate a QTY through sumifs formula with 2 criteria. I have 6000 parts and 10 products i am comparing the network file against. the network file is about 70,000 lines. i have tried looping the formula several ways. one line item and product at a time. one line item and all 10 products. all line items and 1 product, all line and all products. either way it is taking 25 minutes to calculate. then i have to format the cells against its required qty to what has been issued to the product. i have posted my code below. I had to delete path and file specific names,


Sub Populate()

Dim AC As String

Dim ACcount As Integer

Dim Parts As Integer

Dim i As Integer

Dim Qty As Long

Dim DataP As Double

Dim Cell As String

Dim P As Integer


Application.ScreenUpdating = False


Workbooks.Open Filename:= _

"network File"

Windows("network file").Activate


Range("A1").Select

DataP = Range(Selection, Selection.End(xlDown)).Count


Workbooks("current file").Activate

Sheets("sheet i am using in current file").Select


Range("H1").Select 'count products

ACcount = Range(Selection, Selection.End(xlToRight)).Count

Range("A2").Select


Parts = Range(Selection, Selection.End(xlDown)).Count ' counts line items


Range("H1").Select


' Range("A1").Select

'ActiveCell.Offset(0, i + 7).Select 'sets starting point for calculation against network file

Application.Calculation = xlCalculationManual

' AC = ActiveCell.Value

ActiveCell.Offset(1, 0).Select

For P = 1 To Parts

ActiveCell.Range(Cells(P, 1), Cells(P, ACcount)).Formula = _

"=SUMIFS('[network file.xlsm]Data'!R1C10:R" & DataP & "C10,'[network file.xlsm]Data'!R1C8:R" & DataP & "C8,RC1,'[network file.xlsm]Data'!R1C21:R" & DataP & "C21,R1C)"


' ActiveCell.Range(Cells(P, 1), Cells(1, ACcount)).Value = ActiveCell.Range(Cells(P, 1), Cells(1, ACcount)).Value

Qty = ActiveCell.Offset(P - 1, -3).Value 'this section formats cells. "i have tried removing this and just get the numbers to make it faster, but to no avail."


For i = 1 To ACcount


If ActiveCell.Offset(P - 1, i - 1).Value < Qty Then

'Red

ActiveCell.Offset(P - 1, i - 1).Interior.Color = 192


End If

If ActiveCell.Offset(P - 1, i - 1).Value >= Qty Then

'Green

ActiveCell.Offset(P - 1, i - 1).Interior.Color = 5287936

End If


Next

Next


'removing formula and setting values


ActiveCell.Range(Cells(1, 1), Cells(Parts, ACcount)).Value = ActiveCell.Range(Cells(1, 1), Cells(Parts, ACcount)).Value

Workbooks("network file".xlsm").Close savechanges:=False

Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True

End Sub

 

[Trader273]

just some quick observations:


1) Near the end of the code, where you color the cells, I would just use the conditional formatting thats built into excel. I always try to use as much that is native to excel as i can


2) I havent really dove into this code, but seems to be a of "select". Theres no need to select it in vba, as you can still reference it.


maybe if I have some time later I will look at more, but reading it as its formatted on the forum isn't the greatest (ie no idents, coloring, etc)

 

[greg.begin]

sorry about the format, it pasted that way and i didn't adjust it. i tried using the conditional format and becuase of the size of the range, it really bogs the spreadsheet. can you assist on the reference concepts. that would be very helpful. I think that that concept is my crux right now.

 

[NARAYANK991]

Hi ,


In case anyone wants to debug the code , here it is , in indented form :

[pre]

Sub Populate()
Dim AC As String
Dim Cell As String

Dim ACcount As Integer
Dim i As Integer
Dim P As Integer
Dim Parts As Integer

Dim Qty As Long

Dim DataP As Double

Application.ScreenUpdating = False
'---------------------------------------------------------------------------------------------------------------
Workbooks.Open Filename:="network File"
Windows("network file").Activate
Range("A1").Select
DataP = Range(Selection, Selection.End(xlDown)).Count

Workbooks("current file").Activate
Sheets("sheet i am using in current file").Select

Range("H1").Select 'count products
ACcount = Range(Selection, Selection.End(xlToRight)).Count
Range("A2").Select

Parts = Range(Selection, Selection.End(xlDown)).Count ' counts line items

Range("H1").Select

' Range("A1").Select
'ActiveCell.Offset(0, i + 7).Select 'sets starting point for calculation against network file
Application.Calculation = xlCalculationManual
' AC = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
'---------------------------------------------------------------------------------------------------------------
For P = 1 To Parts
ActiveCell.Range(Cells(P, 1), Cells(P, ACcount)).Formula = _
"=SUMIFS('[network file.xlsm]Data'!R1C10:R" & DataP & "C10,'[network file.xlsm]Data'!R1C8:R" & DataP & "C8,RC1,'[network file.xlsm]Data'!R1C21:R" & DataP & "C21,R1C)"

' ActiveCell.Range(Cells(P, 1), Cells(1, ACcount)).Value = ActiveCell.Range(Cells(P, 1), Cells(1, ACcount)).Value
Qty = ActiveCell.Offset(P - 1, -3).Value 'this section formats cells. "i have tried removing this and just get the numbers to make it faster, but to no avail."

For i = 1 To ACcount
If ActiveCell.Offset(P - 1, i - 1).Value < Qty Then
'Red
ActiveCell.Offset(P - 1, i - 1).Interior.Color = 192
End If
If ActiveCell.Offset(P - 1, i - 1).Value >= Qty Then
'Green
ActiveCell.Offset(P - 1, i - 1).Interior.Color = 5287936
End If
Next
Next
'-----------------------------------------------------------------------------------------------------------------
'removing formula and setting values
ActiveCell.Range(Cells(1, 1), Cells(Parts, ACcount)).Value = ActiveCell.Range(Cells(1, 1), Cells(Parts, ACcount)).Value
Workbooks("network file.xlsm").Close savechanges:=False
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

[/pre]
Narayan