VBA Formula

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,153
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
261
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
42,153
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
261
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
42,153
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
261
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,496
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
261
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
42,153
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,081,415
Messages
5,358,527
Members
400,501
Latest member
price83

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top