Problem with VBA Loop

cfadr

New Member
Joined
Oct 17, 2019
Messages
28
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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:
Code:
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
 
Last edited:
Upvote 0
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
 
Upvote 0
You can do the whle calculation in VBA which will be much faster:
try this
Code:
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]
 
Last edited:
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top