Insert string based on column criteria

DaveMiles

New Member
Joined
Jun 22, 2018
Messages
2
Code:
r = ActiveSheet.Cells(Rows.Count, "G").End(xlUp).Row
 
For n = 1 To r
Dim s As String
Select Case Range("H",”I” & n).Text
Case "<>", "<>": s = "Open New"
End Select
If s <> "" Then Range("J" & n) = s
Next
Every row in Column G has a sequential valued that recurs. Sometimes the same row in Column H has a string and sometimes the same row in Column I has a string for one or all of the rows of the recurring value. If Column H or Column I has a string in any row that Column G has a recurring value then I want to enter the text ‘Open New’ into Column J for every row of the recurring value in Column G. As you can see, I am stuck, can you help?
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
2,650
What is meant by "a sequential value that recurs"?
Post some sample data.
You can probably achieve what you want with a formula in column J.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,169
Office Version
365
Platform
Windows
Welcome to the MrExcel board!

As footoo has mentioned this should be feasible with a formula. For example below, if I have interpreted your description correctly.

Excel Workbook
GHIJ
1
21
31Open New
41abcOpen New
51Open New
61Open New
72
82
93Open New
103xyzOpen New
114Open New
124Open New
134sssOpen New
144dfgOpen New
154Open New
165
175
185
19
Open New



If that is the result you want but it needs to be by vba then one way would be to incorporate that formula solution into your vba:
Code:
Sub Open_New()
  Dim lr As Long
  
  lr = Range("G" & Rows.Count).End(xlUp).Row
  With Range("J2:J" & lr)
    .Formula = Replace("=IF(SUMPRODUCT(--(G$2:G$#=G2),--(H$2:H$#&I$2:I$#<>"""")),""Open New"","""")", "#", lr)
    .Value = .Value
  End With
End Sub
 

Forum statistics

Threads
1,081,543
Messages
5,359,431
Members
400,526
Latest member
Brook1083

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