VBA Formula

[Vishaal]

Hi All

Thanks in advance,

I have the following sheet

Excel 2010 32 bit

	A​	B​	C​	D​	E​
1​	S.No.​	25689​	365478​	2569​	25698​
2​	​	2564​	2145​	1425​	1478​
3​	​	​	​	​	​
4​	1​	25​	5​	8​	20​
5​	2​	30​	10​	10​	23​
6​	3​	35​	20​	12​	26​
7​	4​	40​	30​	14​	29​
8​	5​	45​	40​	16​	31​
9​	6​	50​	50​	18​	33​
10​	7​	55​	​	19​	36​
11​	8​	60​	​	​	39​
12​	9​	65​	​	​	41​
13​	10​	70​	​	​	42​
14​	11​	​	​	​	43​
15​	12​	​	​	​	​

Sheet: Sheet1

and need the following result in same sheet

Excel 2010 32 bit

	A​	B​	C​	D​	E​
1​	S.No.​	25689​	365478​	2569​	25698​
2​	​	2564​	2145​	1425​	1478​
3​	​	​	​	​	​
4​	1​	​	​	​	​
5​	2​	​	​	​	​
6​	3​	​	Yes​	​	​
7​	4​	​	Yes​	Yes​	​
8​	5​	​	Yes​	Yes​	​
9​	6​	​	Yes​	Yes​	​
10​	7​	Yes​	​	Yes​	​
11​	8​	Yes​	​	​	Yes​
12​	9​	Yes​	​	​	Yes​
13​	10​	Yes​	​	​	Yes​
14​	11​	​	​	​	Yes​
15​	12​	​	​	​	​

Sheet: Sheet1

We have added the "Four Yes" from bottom in every column and removed the all numbers

Pls provide the solution

 

[Vishaal]

Thank you very much Peter_SSs Sir Ji for the help

its a proud that i m a member of a great forum

 

[Vishaal]

Use this code instead of what I gave you earlier and replace the red number 4 with the number of yeses you want...

Sub YesLast4Max()
  Dim LR As Long, C As Long, Yeses As Long
  Yeses = [B][COLOR=#FF0000][SIZE=2]4[/SIZE][/COLOR][/B]
  For C = 2 To Cells(4, Columns.Count).End(xlToLeft).Column
    If Len(Cells(4, C)) Then
      LR = Columns(C).Find("*", , xlValues, , xlRows, xlPrevious).Row
      With Range(Cells(4, C), Cells(LR, C))
        .Value = Evaluate("IF(ROW(" & .Address & ")>MAX(3," & LR & "-" & Yeses & "),""Yes"","""")")
      End With
    End If
  Next
End Sub

Thank you very much Rick Rothstein
Sir Ji for the help

its a proud that i m a member of a great forum