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]

What's the connection between this:

In this case, E2 should not be copied because E7 (272) - E22 (0 because is empty) is >0 , i need to copy E7 in A7 only when is <0 , same as E7-E33 .

and your original post:

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

You see how getting the columns muddled up can lead to confusion?

E2 should not be copied

In the file you shared, E2 contains the value "10115", and it isn't copied anywhere!

E7 (272)

In the file you shared, E7 has the value 247 not 272.

I think it might be easier if you shared your file again with at least the first half dozen actual results you're expecting in column A - that way we might be able to figure out exactly what you're looking for...

 

[kevin9999]

I'm about to log off for the night. Whatever you post I'll look at tomorrow. Just out of interest, did you try the formula approach suggested in post #16?

 

[Seb1991]

Im sorry i made a bit a mess in my last reply, in this case E2 should not be copied because L7 -L22 >0 and same for L7-L33. I want it to be copied in A2 only if one of the 2 differences were <0. Every 45 rows from these cells there are the same things, so i need the formula to work after 45 rows for every cell after the first E2 to A2 if L7-L22<0 or L7-L33<0 (E47 copied in A47 if L52-L67<0 or L52-L78<0) I really appreciate your help im sorry if i have been messy

 

[kevin9999]

Hi Seb,

Please find the revised code for your project. I noticed (and checked) that none of the product codes met the conditions for returning the codes because not one of them had a balance <0 on the 2 conditions met. Let me know if you disagree and I will forward you my test pattern. If it's true that none of the conditions were going to return a result - it might have been helpful to know this from the start.

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:E" & 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, 1) - a(i + 20, 1) < 0 Or a(i + 5, 1) - a(i + 31, 1) < 0) Then b(i, 1) = a(i, 1)
    Next i
   
    ws.Range("A2").Resize(UBound(b, 1)).Value = b
End Sub

 

[Seb1991]

i made another file with 2 windows, before and after, how it should be, with highlighted cells so it's easier to understand and with <0 and >0 so you can see when it should work and when it should not, ty for the advice when i use the code you posted, it doesnt work.

MACRO daily cuts.xlsx

Shared with Dropbox

www.dropbox.com

 

[kevin9999]

It didn't work because you keep switching your advice from comparing column E figures with column L figures. The attached file switches back to column L figures and I tested it on your latest download. It does work now - I've left the results in the BEFORE sheet. Before you test this on your actual data, YOU MUST CHANGE THE NAME OF THE SHEET FROM "BEFORE" TO THE ACTUAL SHEET NAME. I advised you to do this in post #8:

Change the worksheet name to whatever you call it.

and you didn't do it. Don't do it again, and the code won't work.

I'm sharing the file here for your perusal. Good Luck!

MACRO daily cuts.xlsm

 

[kevin9999]

Just wondering if you tried the code in post #26 before you cross-posted?
Copy cell value in Column A if 2 SUM <0

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Copy cell value in Column A if 2 SUM <0
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Seb1991]

Just wondering if you tried the code in post #26 before you cross-posted?
Copy cell value in Column A if 2 SUM <0

Hi, yes i tried, i don't know why the macro works in the file, but when i copy paste my 70k rows it doesnt work anymore, i cross posted because i didn't want to bother you guys more than i've done already, since im very confused on methods and stuff like that. I really don't know what im doing wrong tbh, i changed the name, everything you told me to do... Debug underlines this line

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)

every row seems to me to be identical to the others

 

[kevin9999]

but when i copy paste my 70k rows it doesnt work anymore

I think we've reached an impasse with this. As I hope you saw on the file I shared, the code does work on the data you shared in post #25. The only way I see going forward is if you can possibly share your entire file and I'll try and adapt the code to that. Other than that, I'll have to withdraw unfortunately