VBA Formula

Vishaal · Oct 10, 2019

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]

2564

[/td][td]

2145

[/td][td]

1425

[/td][td]

1478

[/td][/tr]

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

3

[/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]

19

[/td][td]

36

[/td][/tr]

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

11

[/td][td]

8

[/td][td]

60

[/td][td]

39

[/td][/tr]

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

12

[/td][td]

9

[/td][td]

65

[/td][td]

41

[/td][/tr]

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

13

[/td][td]

10

[/td][td]

70

[/td][td]

42

[/td][/tr]

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

14

[/td][td]

11

[/td][td]

43

[/td][/tr]

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

15

[/td][td]

12

[/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]

2564

[/td][td]

2145

[/td][td]

1425

[/td][td]

1478

[/td][/tr]

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

3

[/td][td]

[/td][/tr]

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

4

[/td][td]

1

[/td][td]

[/td][/tr]

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

5

[/td][td]

2

[/td][td]

[/td][/tr]

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

6

[/td][td]

3

[/td][td]

Yes

[/td][td]

[/td][/tr]

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

7

[/td][td]

4

[/td][td]

Yes

[/td][td]

Yes

[/td][td]

[/td][/tr]

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

8

[/td][td]

5

[/td][td]

Yes

[/td][td]

Yes

[/td][td]

[/td][/tr]

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

9

[/td][td]

6

[/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]

Yes

[/td][td]

[/td][/tr]

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

11

[/td][td]

8

[/td][td]

Yes

[/td][td]

Yes

[/td][/tr]

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

12

[/td][td]

9

[/td][td]

Yes

[/td][td]

Yes

[/td][/tr]

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

13

[/td][td]

10

[/td][td]

Yes

[/td][td]

Yes

[/td][/tr]

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

14

[/td][td]

11

[/td][td]

Yes

[/td][/tr]

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

15

[/td][td]

12

[/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

Peter_SSs · Oct 10, 2019

Vishaal said:
Hi, the above vba code are working fine for the above given data but when i am adding some coloumns and row, coloumn AtoCB and rows 1to25

The above code is not working

Help pls

In my code you will see
For c = 2 to 5
That means for columns 2 to 5 which is columns B to E. You should be able to make the adjustment if it is a one-off

If the number of columns is continuously changing & row 1 can be used to determine the last column then use

Code:

For c = 2 To Cells(1, Columns.Count).End(xlToLeft).Column

Vishaal · Oct 10, 2019

One more problem with code when we are trying after changing c 2 to 200,all "Yes" are starts from last, i mean in every coloumn supoose we have 16 rows then all "Yes" are pasted from row 13 in every coloumn

Peter_SSs · Oct 11, 2019

Vishaal said:
.. supoose we have 16 rows then all "Yes" are pasted from row 13 in every coloumn

If you have 16 rows of data in every column then you want Yes from row 13 to 16 in every column don't you? The last 4 rows of data should be replaced with 'Yes'?

Vishaal · Oct 11, 2019

Hi,

After using the code we are getting the following result

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][th]

F

[/th][th]

G

[/th][th]

H

[/th][th]

I

[/th][th]

J

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

1

[/td][td]

[/td][/tr]

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

2

[/td][td]

[/td][/tr]

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

3

[/td][td]

[/td][/tr]

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

4

[/td][td]

1

[/td][td]

[/td][/tr]

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

5

[/td][td]

2

[/td][td]

[/td][/tr]

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

6

[/td][td]

3

[/td][td]

[/td][/tr]

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

7

[/td][td]

4

[/td][td]

[/td][/tr]

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

8

[/td][td]

5

[/td][td]

[/td][/tr]

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

9

[/td][td]

6

[/td][td]

[/td][/tr]

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

10

[/td][td]

7

[/td][td]

[/td][/tr]

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

11

[/td][td]

8

[/td][td]

[/td][/tr]

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

12

[/td][td]

9

[/td][td]

[/td][/tr]

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

13

[/td][td]

10

[/td][td]

[/td][/tr]

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

14

[/td][td]

11

[/td][td]

[/td][/tr]

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

15

[/td][td]

12

[/td][td]

[/td][/tr]

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

16

[/td][td]

13

[/td][td]

[/td][/tr]

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

17

[/td][td]

14

[/td][td]

[/td][/tr]

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

18

[/td][td]

15

[/td][td]

[/td][/tr]

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

19

[/td][td]

16

[/td][td]

[/td][/tr]

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

20

[/td][td]

17

[/td][td]

[/td][/tr]

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

21

[/td][td]

18

[/td][td]

[/td][/tr]

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

22

[/td][td]

19

[/td][td]

[/td][/tr]

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

23

[/td][td]

20

[/td][td]

[/td][/tr]

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

24

[/td][td]

21

[/td][td]

Yes

[/td][td]

Yes

[/td][td]

Yes

[/td][td]

Yes

[/td][td]

Yes

[/td][td]

Yes

[/td][td]

Yes

[/td][td]

Yes

[/td][td]

Yes

[/td][/tr]

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

25

[/td][td]

22

[/td][td]

Yes

[/td][td]

Yes

[/td][td]

Yes

[/td][td]

Yes

[/td][td]

Yes

[/td][td]

Yes

[/td][td]

Yes

[/td][td]

Yes

[/td][td]

Yes

[/td][/tr]

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

26

[/td][td]

23

[/td][td]

Yes

[/td][td]

Yes

[/td][td]

Yes

[/td][td]

Yes

[/td][td]

Yes

[/td][td]

Yes

[/td][td]

Yes

[/td][td]

Yes

[/td][td]

Yes

[/td][/tr]

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

27

[/td][td]

24

[/td][td]

Yes

[/td][td]

Yes

[/td][td]

Yes

[/td][td]

Yes

[/td][td]

Yes

[/td][td]

Yes

[/td][td]

Yes

[/td][td]

Yes

[/td][td]

Yes

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

but i want the following results

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][th]

F

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

1

[/td][td]

[/td][/tr]

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

2

[/td][td]

[/td][/tr]

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

3

[/td][td]

[/td][/tr]

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

4

[/td][td]

1

[/td][td]

[/td][/tr]

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

5

[/td][td]

2

[/td][td]

[/td][/tr]

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

6

[/td][td]

3

[/td][td]

Yes

[/td][td]

[/td][/tr]

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

7

[/td][td]

4

[/td][td]

Yes

[/td][td]

[/td][/tr]

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

8

[/td][td]

5

[/td][td]

Yes

[/td][td]

[/td][/tr]

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

9

[/td][td]

6

[/td][td]

Yes

[/td][td]

[/td][/tr]

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

10

[/td][td]

7

[/td][td]

[/td][/tr]

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

11

[/td][td]

8

[/td][td]

[/td][/tr]

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

12

[/td][td]

9

[/td][td]

[/td][/tr]

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

13

[/td][td]

10

[/td][td]

Yes

[/td][td]

[/td][/tr]

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

14

[/td][td]

11

[/td][td]

Yes

[/td][td]

Yes

[/td][/tr]

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

15

[/td][td]

12

[/td][td]

Yes

[/td][td]

Yes

[/td][/tr]

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

16

[/td][td]

13

[/td][td]

Yes

[/td][td]

Yes

[/td][/tr]

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

17

[/td][td]

14

[/td][td]

Yes

[/td][/tr]

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

18

[/td][td]

15

[/td][td]

[/td][/tr]

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

19

[/td][td]

16

[/td][td]

[/td][/tr]

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

20

[/td][td]

17

[/td][td]

Yes

[/td][td]

[/td][/tr]

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

21

[/td][td]

18

[/td][td]

Yes

[/td][td]

[/td][/tr]

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

22

[/td][td]

19

[/td][td]

Yes

[/td][td]

Yes

[/td][td]

[/td][/tr]

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

23

[/td][td]

20

[/td][td]

Yes

[/td][td]

Yes

[/td][td]

[/td][/tr]

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

24

[/td][td]

21

[/td][td]

Yes

[/td][td]

[/td][/tr]

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

25

[/td][td]

22

[/td][td]

Yes

[/td][td]

[/td][/tr]

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

26

[/td][td]

23

[/td][td]

[/td][/tr]

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

27

[/td][td]

24

[/td][td]

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

help pls

Peter_SSs · Oct 11, 2019

In that case I'm guessing that B4:F27 now contain formulas whereas before I think that must not have been the case as you said the code was working then.

Try making this change

Code:

<del>lr = Cells(Rows.Count, c).End(xlUp).Row</del>
lr = Columns(c).Find(What:="?*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Vishaal · Oct 11, 2019

Thank you very much Peter_SSs ji

Its working

Thanks for your code and giving me time

Rick Rothstein · Oct 11, 2019

Here is another macro that should work...

Code:

Sub YesLast4()
  Dim LR As Long, C As Long
  For C = 2 To Range("A1").End(xlToRight).Column
    LR = Columns(C).Find("*", , xlValues, , xlRows, xlPrevious).Row
    With Range(Cells(4, C), Cells(LR, C))
      .Value = Evaluate("IF(ROW(" & .Address & ")>MAX(3," & LR & "-4),""Yes"","""")")
    End With
  Next
End Sub

Vishaal · Oct 11, 2019

Thank you Rick Rothstein Ji,

Thanks for your code and giving time on my post.

Peter_SSs · Oct 11, 2019

Vishaal said:
Thank you very much Peter_SSs ji

Its working

Thanks for your code and giving me time

You're welcome. However, I have realised that my code will fail you if it is possible that one of the intermediate columns can have no numbers at all, just formulas returning "".
In case that is possible, a correction is ..

Rich (BB code):

Sub YesLast4_v2()
  Dim c As Long, fr As Long, lr As Long
  Const FirstDataRow As Long = 4
  
  For c = 2 To Cells(1, Columns.Count).End(xlToLeft).Column
    lr = Columns(c).Find(What:="?*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    If lr >= FirstDataRow Then
      fr = IIf(lr - 3 < FirstDataRow, FirstDataRow, lr - 3)
      Cells(fr, c).Resize(lr - fr + 1).Value = "Yes"
      If fr > FirstDataRow Then Cells(FirstDataRow, c).Resize(fr - FirstDataRow).ClearContents
     End If
  Next c
End Sub

BTW, if it is possible to have an 'empty' column in your data, Rick's code will put a Yes in row 4 of that column & delete the row 2 value, though I think this addition should solve that

Rich (BB code):

If lr > 3 Then .Value = Evaluate("IF(ROW(" & .Address & ")>MAX(3," & lr & "-4),""Yes"","""")")

On further consideration of your data and layout it appears that in a column, if there are any numbers then there are no gaps in those numbers. If that is the case and your 'numbers' always start in row 4 of column B then I think you could process all the columns at once should you so want.

Rich (BB code):

Sub YesLast4_v3()
  With ActiveSheet.UsedRange.Offset(3, 1)
    .Value = Evaluate(Replace(Replace("if(#="""","""",if(@="""",""Yes"",""""))", "#", .Address), "@", .Offset(4).Address))
  End With
End Sub

Vishaal · Oct 11, 2019

Thanks Peter_SSs ji,

I will check and update u

VBA Formula

Vishaal

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Excel Facts

Vishaal

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Vishaal

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Vishaal

Well-known Member

Rick Rothstein

MrExcel MVP

Vishaal

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Vishaal

Well-known Member

Similar threads

Forum statistics

Share this page

VBA Formula

Well-known Member

MrExcel MVP, Moderator

Excel Facts

Well-known Member

MrExcel MVP, Moderator

Well-known Member

MrExcel MVP, Moderator

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP, Moderator

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock