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!

 

[kevin9999]

How does this part:

if (L7-L22<0) or (L7-L33<0),

increment (if at all) as you jump down by 45 rows in column E?

 

[Seb1991]

How does this part:

increment (if at all) as you jump down by 45 rows in column E?

Increment also, my bad i missed that. So yes, L52-L67<0 or L7-L78 etc, sorry again

 

[kevin9999]

or L7-L78

Shouldn't that be L52-L78?

 

[kevin9999]

Shouldn't that be L52-L78?

Forget that

 

[Seb1991]

Forget that

I have 2 values of course, i didnt explained, in my job i need to daily check the stock i have to be more than my prevision or my preorder, so i want to know if i dont have the stock for the day and i need to cut some goods :D L7 is my daily stock, L22 preorders and L33 prevision. Im new in my company and i wanto to impress, since everyone here are old dinosaurs who still check data code by code xD no way im gonna waste half of my day doing that way

 

[Seb1991]

I have 2 values of course, i didnt explained, in my job i need to daily check the stock i have to be more than my prevision or my preorder, so i want to know if i dont have the stock for the day and i need to cut some goods :D L7 is my daily stock, L22 preorders and L33 prevision. Im new in my company and i wanto to impress, since everyone here are old dinosaurs who still check data code by code xD no way im gonna waste half of my day doing that way

in case the value is negative, it means i don't have enough stock, that's why i want to copy the value E2 (the product code) in A so i can filter, remove the empty rows, so i'll have every code under stock, easy and fast, but im new into vba im still learning, that's why i asked for help, it's super cool that people with advanced knowledge help other people like me :D

 

[kevin9999]

My gut feeling is that this is going to take a few goes to get right, but as a starting point, try the following on a copy of your worksheet. Change the worksheet name to whatever you call it.

Option Explicit
Sub Seb1991_V1()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")   '<~~ *** Change to actual sheet name ***
    Dim LRow As Long
    LRow = ws.Cells(Rows.Count, "E").End(xlUp).Row
    
    Dim a, b, i As Long
    a = ws.Range("A2:L" & LRow + 32)
    ReDim b(1 To UBound(a, 1), 1 To 1)
    
    For i = 1 To LRow
        If a(i, 5) <> "" And (a(i + 5, 12) - a(i + 20, 12) < 0 Or a(i + 5, 12) - a(i + 31, 12) < 0) Then b(i, 1) = a(i, 5)
    Next i
    
    ws.Range("A2").Resize(UBound(b, 1)).Value = b
End Sub

 

[Seb1991]

It says Error 9 (Index not in the range)

 

[kevin9999]

It says Error 9 (Index not in the range)

On what line?