Log In
Register
Home
April 18, 2020
Offline
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
Trusted Members
December 20, 2019
Offline
Can you add the workbook so we dont need to recreate it?
Purfleet
April 18, 2020
Offline
Hi Mr Purfleet
April 18, 2020
Offline
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
October 5, 2010
Offline
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
April 18, 2020
Offline
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
1 Guest(s)
