Hi every one could some one help me
i want to put and merge formula in column M:
=IF(M2=L2,"",M2) in this below code
i think i should put after or merge it with This row: range ("m2:m" & lr)="=L2-E2
it is obvious but if you need i will attach the excel sheet
Thank you so much
Sub SUBTRACT & AVERAGE ()
lr = Cells(Rows.Count, "a").End(xlUp).Row
Set yl = Range("b1:b" & lr)
yl.Copy Range("q1")
Range("m2:m" & lr) = "=L2-E2"
Range("q1:q" & lr).RemoveDuplicates Columns:=1, Header:=xlYes
clr = Cells(Rows.Count, "q").End(xlUp).Row
Range("r1") = "Average"
Range("r2:r" & clr) = "=AVERAGEIFS($M$2:$M$" & lr & ",$B$2:$B$" & lr & ",Q2)"
End Sub
Can you add the workbook so we dont need to recreate it?
Purfleet
Hi Mr Purfleet
Hi
what i mean is
=if(L2-E2=L2;true [BLANK];false[L2-E2])
or
=if(l2-e2=l2;"";if(l2-e2<>l2;"l2-e2'))
or
Range("m2:m" & LR) = "=IF(L2-E2=L2,"",L2-E2)"
could you please check syntax
thank you so much
Hi mm,
Thanks for the workbook but it's an XLSX so we'd still need to copy/paste your code from the forum into it to test it out 🙂 Please supply the XLSM file if you need us to look at your code.
I'm not sure why you are turning to VBA for this? maybe I don't have the full picture if what you are doing.
I've turned your data into a table to that when you enter the formula in Col M it is copied down to all rows.
You can use UNIQUE to get a unique list of values from the sal1 column, and then the AVERAGEIFS formula in Col Ris as you have it in your VBA.
Regards
Phil
hi
thank you Mr Treacy
i appreciate your help
some one else helped me in this issue and finally i found it
i wanted this below formula
Range("m2:m" & LR) = "=IF(L2-E2=L2 ,"""",IF(L2-E2=-E2,"""",L2-E2))"
it has been solved
