VBA Formula

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,245
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
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Vishaal

Active Member
Joined
Mar 16, 2019
Messages
371
Office Version
2010, 2007
Platform
Windows, Web
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
45,245
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
371
Office Version
2010, 2007
Platform
Windows, Web
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
45,245
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
371
Office Version
2010, 2007
Platform
Windows, Web
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
36,044
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
371
Office Version
2010, 2007
Platform
Windows, Web
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
45,245
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
 

Vishaal

Active Member
Joined
Mar 16, 2019
Messages
371
Office Version
2010, 2007
Platform
Windows, Web
Thanks Peter_SSs ji,

I will check and update u
 

Watch MrExcel Video

Forum statistics

Threads
1,102,630
Messages
5,487,988
Members
407,617
Latest member
Samanthad2007

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top