Insert string based on column criteria

[DaveMiles]

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]

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]

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
	G	H	I	J
1
2	1			Open New
3	1			Open New
4	1	abc		Open New
5	1			Open New
6	1			Open New
7	2
8	2
9	3			Open New
10	3		xyz	Open New
11	4			Open New
12	4			Open New
13	4		sss	Open New
14	4	dfg		Open New
15	4			Open New
16	5
17	5
18	5
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:

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

 

[DaveMiles]

SOLVED: Insert string based on column criteria

Thank you Peter_SSs

 

[Peter_SSs]

Re: SOLVED: Insert string based on column criteria

Thank you Peter_SSs

You are very welcome.