VBA Formula

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,801
Office Version
365
Platform
Windows
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

Active Member
Joined
Mar 16, 2019
Messages
260
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,801
Office Version
365
Platform
Windows
.. 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

Active Member
Joined
Mar 16, 2019
Messages
260
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,801
Office Version
365
Platform
Windows
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

Active Member
Joined
Mar 16, 2019
Messages
260
Thank you very much Peter_SSs ji

Its working

Thanks for your code and giving me time
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,431
Office Version
2010
Platform
Windows
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

Active Member
Joined
Mar 16, 2019
Messages
260
Thank you Rick Rothstein Ji,

Thanks for your code and giving time on my post.


 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,801
Office Version
365
Platform
Windows
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
 

Forum statistics

Threads
1,078,466
Messages
5,340,481
Members
399,378
Latest member
voodoo1

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top