VBA If Statement Loop Not Working

[cfadr]

Hi,

I am trying to loop through some data and apply tests to it (i.e. if values are above/below certain input thresholds then paste a 1 or 0 into another part of the workbook). Unfortunately my code below doesn't appear to be working but I'm not getting anywhere debugging - can anyone please help/suggest where I have gone wrong?

Thanks

Sub flag_analysis()
Application.ScreenUpdating = False

Dim data_set As Variant
data_set = Sheets("Data").Range("A5:F20").Value
Dim i As Long, flag_activation As Long

flag1_level = Sheets("Inputs").Range("D3")   'this is my first test - an input with the threshold level (set to 40)
flag1_level = Sheets("Inputs").Range("D4")   'this is the second test - an input which is a percentagee e.g. 10%

For i = 1 To UBound(data_set, 1)
flag1 = data_set(i, 5) 'this is the data I am applying my first test to
flag2 = data_set(i, 6) ' this is the data I am applying my second test to
    If (IsNumeric(flag1) And flag1 >= flag1_level) Or (IsNumeric(flag2) And flag2 >= flag2_level) Then 'if the data is numeric (to avoid any cells with errors in them) and if either the first or the second test is satisfied then...
    flag_activation = 1 'set the flag equal to 1 (I am not using booleans because I want to add the number of flags later)
    Else: flag_activation = 0 'else set the flag equal to zero
    Sheets("Flags").Range("E" & i + 4) = flag_activation 'copy the flag value into the sheet Flags
    End If
    
flag_activation = 0  'reset the flag at zero for the next loop

Next i

End Sub

 

[xenou]

Looks like flag2_level is not defined (two variables with flag1_level are defined instead):

flag1_level = Sheets("Inputs").Range("D3")   'this is my first test - an input with the threshold level (set to 40)
flag1_level = Sheets("Inputs").Range("D4")   'this is the second test - an input which is a percentagee e.g. 10%

 

[cfadr]

Sorry that was a typo before I sent; you are correct but it's not working anyway even with that rectified.

Here is the data I am testing against values of 40 (flag 1 level) and 20% (flag 2 level) -

42​	10%​
38​	15%​
0​	23%​
4​	11%​
50​	12%​

And I am just getting zeros and gaps in the output (on the Flags sheet) -

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

Thanks

 

[JackDanIce]

Replace all of your code with this and try:

Sub Flag_Analysis1()

    With Sheets("Flags").Cells(5, 5).Resize(16)
        .Formula = "=--(OR(AND(ISNUMBER(Data!E5),Inputs!$D$3>=Data!$E5),AND(ISNUMBER(Data!F5),Inputs!$D$4>=Data!$F5)))"
        .Value = .Value
    End With
   
End Sub

 

[cfadr]

Thanks that worked. Just to clarify something - I had deliberately avoided using .Formula as you did because I need to run the code on a lot of data (thousands of rows) and I thought using this would slow it down? the whole reason I am using VBA is I had a workbook with a lot of if statements that became unusable.

Thanks

 

[JackDanIce]

You're welcome.

About to head out, if the code is slow, can suggest something closer to your approach when I'm back.

 

[xenou]

The IF-End If turns out to be wrong I think - the output to the flags sheet is only occurring in the Else part. See below for a fix:

    flag1 = data_set(i, 5) 'this is the data I am applying my first test to
    flag2 = data_set(i, 6) ' this is the data I am applying my second test to
   
    If (IsNumeric(flag1) And flag1 >= flag1_level) Or (IsNumeric(flag2) And flag2 >= flag2_level) Then 'if the data is numeric (to avoid any cells with errors in them) and if either the first or the second test is satisfied then...
        flag_activation = 1 'set the flag equal to 1 (I am not using booleans because I want to add the number of flags later)
    Else
        flag_activation = 0 'else set the flag equal to zero
    End If
    Sheets("Flags").Range("E" & i + 4) = flag_activation 'copy the flag value into the sheet Flags