VBA Formula

Vishaal

Well-known Member
Joined
Mar 16, 2019
Messages
530
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web
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
 
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
 
Upvote 0

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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
 
Upvote 0
.. 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'?
 
Upvote 0
Hi,

After using the code we are getting the following result

Excel 2010 32 bit
A
B
C
D
E
F
G
H
I
J
1
2
3
4
1​
5
2​
6
3​
7
4​
8
5​
9
6​
10
7​
11
8​
12
9​
13
10​
14
11​
15
12​
16
13​
17
14​
18
15​
19
16​
20
17​
21
18​
22
19​
23
20​
24
21​
Yes​
Yes​
Yes​
Yes​
Yes​
Yes​
Yes​
Yes​
Yes​
25
22​
Yes​
Yes​
Yes​
Yes​
Yes​
Yes​
Yes​
Yes​
Yes​
26
23​
Yes​
Yes​
Yes​
Yes​
Yes​
Yes​
Yes​
Yes​
Yes​
27
24​
Yes​
Yes​
Yes​
Yes​
Yes​
Yes​
Yes​
Yes​
Yes​
Sheet: Sheet1

but i want the following results

Excel 2010 32 bit
A
B
C
D
E
F
1
2
3
4
1​
5
2​
6
3​
Yes​
7
4​
Yes​
8
5​
Yes​
9
6​
Yes​
10
7​
11
8​
12
9​
13
10​
Yes​
14
11​
Yes​
Yes​
15
12​
Yes​
Yes​
16
13​
Yes​
Yes​
17
14​
Yes​
18
15​
19
16​
20
17​
Yes​
21
18​
Yes​
22
19​
Yes​
Yes​
23
20​
Yes​
Yes​
24
21​
Yes​
25
22​
Yes​
26
23​
27
24​
Sheet: Sheet1

help pls
 
Upvote 0
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
 
Upvote 0
Thank you very much Peter_SSs ji

Its working

Thanks for your code and giving me time
 
Upvote 0
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
 
Upvote 0
Thank you Rick Rothstein Ji,

Thanks for your code and giving time on my post.


 
Upvote 0
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
 
Upvote 0
Thanks Peter_SSs ji,

I will check and update u
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top