VBA Formula

[Vishaal]

Hi All

Thanks in advance,

I have the following sheet

Excel 2010 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]

S.No.​

[/td][td]

25689​

[/td][td]

365478​

[/td][td]

2569​

[/td][td]

25698​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

​

[/td][td]

2564​

[/td][td]

2145​

[/td][td]

1425​

[/td][td]

1478​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

1​

[/td][td]

25​

[/td][td]

5​

[/td][td]

8​

[/td][td]

20​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

2​

[/td][td]

30​

[/td][td]

10​

[/td][td]

10​

[/td][td]

23​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

3​

[/td][td]

35​

[/td][td]

20​

[/td][td]

12​

[/td][td]

26​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]

4​

[/td][td]

40​

[/td][td]

30​

[/td][td]

14​

[/td][td]

29​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]

5​

[/td][td]

45​

[/td][td]

40​

[/td][td]

16​

[/td][td]

31​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]

6​

[/td][td]

50​

[/td][td]

50​

[/td][td]

18​

[/td][td]

33​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]

7​

[/td][td]

55​

[/td][td]

​

[/td][td]

19​

[/td][td]

36​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]

8​

[/td][td]

60​

[/td][td]

​

[/td][td]

​

[/td][td]

39​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td]

9​

[/td][td]

65​

[/td][td]

​

[/td][td]

​

[/td][td]

41​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td]

10​

[/td][td]

70​

[/td][td]

​

[/td][td]

​

[/td][td]

42​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td]

11​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

43​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td]

12​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

and need the following result in same sheet

Excel 2010 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]

S.No.​

[/td][td]

25689​

[/td][td]

365478​

[/td][td]

2569​

[/td][td]

25698​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

​

[/td][td]

2564​

[/td][td]

2145​

[/td][td]

1425​

[/td][td]

1478​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

1​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

2​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

3​

[/td][td]

​

[/td][td]

Yes​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]

4​

[/td][td]

​

[/td][td]

Yes​

[/td][td]

Yes​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]

5​

[/td][td]

​

[/td][td]

Yes​

[/td][td]

Yes​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]

6​

[/td][td]

​

[/td][td]

Yes​

[/td][td]

Yes​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]

7​

[/td][td]

Yes​

[/td][td]

​

[/td][td]

Yes​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]

8​

[/td][td]

Yes​

[/td][td]

​

[/td][td]

​

[/td][td]

Yes​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td]

9​

[/td][td]

Yes​

[/td][td]

​

[/td][td]

​

[/td][td]

Yes​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td]

10​

[/td][td]

Yes​

[/td][td]

​

[/td][td]

​

[/td][td]

Yes​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td]

11​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

Yes​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td]

12​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

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