Problem with VBA Loop

[cfadr]

Hi,

I want to loop through rows in my worksheet and compare the cell values in particular columns with other rows defining a variable as True or False (my segment_trigger variable below) if the values are the same or different.

I have written the following code but its giving me "Compile Error: Object Required" - can someone please tell me where I am going wrong?

Thanks


Sub segment_trigger_returns()
Application.ScreenUpdating = False
Dim segment_trigger As Boolean
Dim trade_date As Date
Set trade_date = Range("A4:A75617")
Dim i As Range
For Each i In trade_date.Rows
i.Cells(1, 3) = company_name_curperiod
i.Cells(-1, 3) = company_name_prevperiod
i.Cells(1, 10) = segments_curperiod
i.Cells(-1, 10) = segments_prevperiod
If company_name_curperiod = company_name_prevperiod And segments_curperiod <> segments_prevperiod Then
segment_trigger = True
Else: segment_trigger = False
Next
Application.ScreenUpdating = True

End Sub

 

[offthelip]

There seemed to be lots of problems with you code, which means I am not entirely clear what you are trying to do. However one thing which is not a good idea is to loop through 75000 rows on a worksheet because that will take along time.
The best way to speed this up is to load the entire worksheet into a variant and array and then do all the checks in memory. I have re written you code to show you how to do that:

Sub segment_trigger_returns()
Application.ScreenUpdating = False
Dim segment_trigger As Boolean
Dim trade_date() As Variant  ' dim trade date as variant array
' load all the data into a variant array
trade_date = Range("A4:J75617") ' Note I have changes this to include column J because it is used lower down
Dim i As Long
For i = 2 To UBound(trade_date, 1) ' start at 2 because you are looking at the cell above
    company_name_curperiod = trade_date(i, 3)
    company_name_prevperiod = trade_date(i - 1, 3)
    segments_curperiod = trade_date(i, 10)
    segments_prevperiod = trade_date(i - 1, 10)
    If company_name_curperiod = company_name_prevperiod And segments_curperiod <> segments_prevperiod Then
     segment_trigger = True ' you don't seem to be doing anything with this flag so it just recalculates everytime and gets lost
    Else
     segment_trigger = False
    End If
Next i


Application.ScreenUpdating = True


End Sub

 

[cfadr]

Thanks very much for this; I am trying to do averageif calculations based on these boolean triggers across 75,000 rows in an efficient manner.

See below I have now tried to use the flag to calculate an average if but it's not working - can anyone suggest where I am going wrong? Thanks

Sub segment_trigger_returns()
Application.ScreenUpdating = False
Dim segment_trigger As Boolean
Dim data_set As Variant
data_set = Range("A4:AV75617")
alpha_1y = Range("S4:S759617")
Dim i As Long
For i = 2 To UBound(data_set, 1)
company_name_curperiod = data_set(i, 3)
company_name_prevperiod = data_set(i - 1, 3)
segments_curperiod = data_set(i, 10)
segments_prevperiod = data_set(i - 1, 10)
If company_name_curperiod = company_name_prevperiod And segments_curperiod <> segments_prevperiod Then
segment_trigger = True
Else: segment_trigger = False
End If
Next i
Sheets("Control").Range("K6") = Application.WorksheetFunction.AverageIfs(alpha_1y, segment_trigger = True)
Application.ScreenUpdating = True
End Sub

 

[offthelip]

You can do the whle calculation in VBA which will be much faster:
try this

Sub segment_trigger_returns()
Application.ScreenUpdating = False
Dim segment_trigger As Boolean
Dim data_set As Variant
data_set = Range("A4:AV75617")
alpha_1y = Range("S4:S759617")
Dim i As Long
Dim sumall As Long
Dim cnt As Long
cnt = 0
sumall = 0


For i = 2 To UBound(data_set, 1)
company_name_curperiod = data_set(i, 3)
company_name_prevperiod = data_set(i - 1, 3)
segments_curperiod = data_set(i, 10)
segments_prevperiod = data_set(i - 1, 10)
If company_name_curperiod = company_name_prevperiod And segments_curperiod <> segments_prevperiod Then
 sumall = sumall + apha_1y(i, 1)
 cnt = cnt + 1
End If
Next i
If cnt > 0 Then
 
Sheets("Control").Range("K6") = sumall / cnt
end if


Application.ScreenUpdating = True
End Sub


/CODE]

 

[cfadr]

Thanks; I tried that but got the error "Sub or Function not defined" so I added the line "Dim alpha_1y As Variant" and now I am getting a "type mismatch" error and I'm not sure why?

Sub segment_trigger_returns()
Application.ScreenUpdating = False
Dim segment_trigger As Boolean
Dim data_set As Variant
Dim alpha_1y As Variant
data_set = Range("A4:AV75617")
alpha_1y = Range("S4:S759617")
Dim i As Long
Dim sumall As Long
Dim cnt As Long
cnt = 0
sumall = 0




For i = 2 To UBound(data_set, 1)
company_name_curperiod = data_set(i, 3)
company_name_prevperiod = data_set(i - 1, 3)
segments_curperiod = data_set(i, 10)
segments_prevperiod = data_set(i - 1, 10)
If company_name_curperiod = company_name_prevperiod And segments_curperiod <> segments_prevperiod Then
sumall = sumall + alpha_1y(i, 1)
cnt = cnt + 1
End If
Next i
If cnt > 0 Then


Sheets("Control").Range("K6") = sumall / cnt
End If




Application.ScreenUpdating = True
End Sub

 

[offthelip]

which line do you get the error on? The "Dim alpha_1y As Variant" line is not actually necessary since excel defaults all variables to variants.
The "Sub or Function not defined" error was due to my misspelling Alpha_1y , I left out the l as you had spotted.
Are there just numbers in column S of your workhseet?

 

[cfadr]

which line do you get the error on? The "Dim alpha_1y As Variant" line is not actually necessary since excel defaults all variables to variants.
The "Sub or Function not defined" error was due to my misspelling Alpha_1y , I left out the l as you had spotted.
Are there just numbers in column S of your workhseet?

There are blank cells and numbers

 

[offthelip]

which line do you get the error on?

 

[cfadr]

It wasn't clear so I amended the code as follows and it tells me I am getting an error on line 18 which is sumall = sumall + alpha_1y(i, 1)

Sub segment_trigger_returns()
On Error GoTo ErrHandler
1 Application.ScreenUpdating = False
2 Dim segment_trigger As Boolean
3 Dim data_set As Variant
4 Dim alpha_1y As Variant
5 data_set = Range("A4:AV75617")
6 alpha_1y = Range("S4:S759617")
7 Dim i As Long
8 Dim sumall As Long
9 Dim cnt As Long
10 cnt = 0
11 sumall = 0
12 For i = 2 To UBound(data_set, 1)
13 company_name_curperiod = data_set(i, 3)
14 company_name_prevperiod = data_set(i - 1, 3)
15 segments_curperiod = data_set(i, 10)
16 segments_prevperiod = data_set(i - 1, 10)
17 If company_name_curperiod = company_name_prevperiod And segments_curperiod <> segments_prevperiod Then
18 sumall = sumall + alpha_1y(i, 1)
19 cnt = cnt + 1
20 End If
21 Next i
22 If cnt > 0 Then
23 Sheets("Control").Range("K6") = sumall / cnt
24 End If
ErrHandler:
MsgBox "An error occurred in line: " & Erl

Application.ScreenUpdating = True
End Sub

 

[cfadr]

Okay it appears to be because some of the values in alpha_1y are not numeric so I've tried to add the IsNumeric criteria but it's now telling the subscript is out of range?

Sub segment_trigger_returns()
Application.ScreenUpdating = False
Dim segment_trigger As Boolean
Dim data_set As Variant
data_set = Range("A4:AV75617")
Dim i As Long


Dim sumall As Double
Dim cnt As Long
cnt = 0
sumall = 0
For i = 2 To UBound(data_set, 1)
company_name_curperiod = data_set(i, 3)
company_name_prevperiod = data_set(i - 1, 3)
segments_curperiod = data_set(i, 10)
segments_prevperiod = data_set(i - 1, 10)
If company_name_curperiod = company_name_prevperiod And segments_curperiod <> segments_prevperiod And IsNumeric(data_set(i, 19)) Then
sumall = sumall + data_set(i, 19)
cnt = cnt + 1
End If
Next i
If cnt > 0 Then
Sheets("Control").Range("K6") = sumall / cnt
End If
Application.ScreenUpdating = True
End Sub