Copy Cell E value every n rows in A if (X-Y)<0 or (X-Z)<0

[Seb1991]

Hi guys, hope you're all fine. I need some help with the following macro
I'd like to copy a value of E2 in column A of the same row (A2) , if (L7-L22<0) or (L7-L33<0), in loop, so E2 in A2, from E2 every 45 rows so E47 in E47, E92 in A92 etc..
This macro should work as long as there is a value in E column (E2, E47, E92....)
I tried several times before asking here but i failed

Can you help me out with this ? Thank you so much for your time and your help!

 

[Seb1991]

Ok here it is, i hope that every time i will copy paste my daily data it wont give me again the error. Its weird since the formula should be fine, the only thing it can change is some new lines but they would be always the same ratio n rows

In E i had to cut all the values

Test2.xlsx

Shared with Dropbox

www.dropbox.com

 

[kevin9999]

OK Seb, I just ran the code on your test file (link below) and it seemed to run just fine. It returned 14 instances of matches meeting the criteria - is that what you were expecting? When you copy your daily data, there shouldn't be a problem as long as the structure is always the same, and you paste it to the same location/cell.

Test2 - Tested.xlsm

 

[Seb1991]

OK Seb, I just ran the code on your test file (link below) and it seemed to run just fine. It returned 14 instances of matches meeting the criteria - is that what you were expecting? When you copy your daily data, there shouldn't be a problem as long as the structure is always the same, and you paste it to the same location/cell.

Test2 - Tested.xlsm

It can be, for sure, but still i have this weird problem, if i copy paste my data on your file, which is exactly the same, it underlines me this:

If a(i, 1) <> "" And (a(i + 5, 8) - a(i + 20, 8) < 0 Or a(i + 5, 8) - a(i + 31, 8) < 0) Then

the only things that changes is the E column, which is filled with different numbers instead of "test", if i paste "test" in all the cells in column E the macro works. I replaced TEST only because i dindn't want to share the product name and code, for obvious reasons, do you think that's the problem?

 

[Seb1991]

Maybe i found the problem, there are few codes that start with + , others with = , replacing these with empty spaces the macro works! I really hope it's just that :D Thank you all for spending your free time just to help a stranger, i appreciate so much!

 

[kevin9999]

I don't understand why you can still be having some problems running the code. The code itself (reproduced below) runs perfectly on the data set you provided, and I don't follow what you mean by

there are few codes that start with + , others with =

Anyhow, if the code does work for you, when you make some adjustments then all is good. Happy to have helped & good luck for the future

Option Explicit
Sub Seb1991_V2()
    Dim ws As Worksheet
    Set ws = Worksheets("Foglio1")
    Dim LRow As Long, n As Long
    LRow = WorksheetFunction.Ceiling(ws.Cells(Rows.Count, "E").End(xlUp).Row, 45)
    n = LRow - 45
    
    Dim a, b
    a = ws.Range("E2:L" & LRow)
    ReDim b(1 To UBound(a, 1), 1 To 1)
    
    Dim i As Long
    For i = 1 To LRow - 45 Step 45
        If a(i, 1) <> "" And (a(i + 5, 8) - a(i + 20, 8) < 0 Or a(i + 5, 8) - a(i + 31, 8) < 0) Then b(i, 1) = a(i, 1)
    Next i
    
    ws.Range("A2").Resize(UBound(b, 1)).Value = b
End Sub