Add a letter to the end of a value based on values in multiple columns

[zack8576]

Hello, see below, I need to add the letter "J" to the end of the value in column D, also changed the No to Yes (If J is already present, or if Yes is present, then do not modify these values)
IF these conditions are met: the word "Brevard" is in column M on the same row, the word "sanitary" is in column N, AND the words "riser" or "cone" is present in column K

How do I write the VBA code for this? Any help is appreciated !

 

[Alex Blakenburg]

Try this:
What do you want to happen if column D does not have a J suffix and Column E = "Yes" ?
(the code below still adds the J)

Sub UpdateSelectedData()

    Dim shtData As Worksheet
    Dim rng As Range
    Dim arr As Variant
    Dim rowLast As Long, i As Long
   
    Set shtData = ActiveSheet
    rowLast = shtData.Cells(Rows.Count, "D").End(xlUp).Row
    Set rng = shtData.Range("D2:N" & rowLast)               ' <--- Assuming first row of data starts at row 2 (heading on row 1)
    arr = rng                                               ' Note column D is Column 1 in the array
   
    For i = 1 To UBound(arr)
        If UCase(arr(i, 10)) = UCase("Brevard") _
            And UCase(arr(i, 11)) = UCase("Sanitary") _
            And (InStr(1, arr(i, 8), "Riser", vbTextCompare) <> 0 _
                Or InStr(1, arr(i, 8), "Cone", vbTextCompare) <> 0) Then
           
               
                If Right(arr(i, 1), 1) <> "J" Then arr(i, 1) = arr(i, 1) & "J"  ' Only add "J" suffix if not already there
                arr(i, 2) = "Yes"
        End If
    Next i
   
    ' write back column D & E values
    rng.Columns(1).Value = Application.Index(arr, 0, 1)
    rng.Columns(2).Value = Application.Index(arr, 0, 2)

End Sub

PS:
You will generally get faster & better answers if you provide sample data in a form that can easily be copied for testing:
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

20220808 VBA Update data using criteria zack8576.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Col1	Col2	Col3	Ref	Y/N	Col6	Col7	Col8	Col9	Col10	Description	Col12	Type	Product Group
2				F145536J	Yes						MH,4'dia,Base,8"w		Brevard	Sanitary
3				F14616J	Yes						MH,4'dia,Riser,8"w		Brevard	Sanitary
4				F14818J	Yes						MH,4'dia,Cone,8"w		Brevard	Sanitary
5				F03957							AGRU LINER - 2mm		Brevard	Sanitary
Data

 

[zack8576]

Try this:
What do you want to happen if column D does not have a J suffix and Column E = "Yes" ?
(the code below still adds the J)

Sub UpdateSelectedData()

    Dim shtData As Worksheet
    Dim rng As Range
    Dim arr As Variant
    Dim rowLast As Long, i As Long
  
    Set shtData = ActiveSheet
    rowLast = shtData.Cells(Rows.Count, "D").End(xlUp).Row
    Set rng = shtData.Range("D2:N" & rowLast)               ' <--- Assuming first row of data starts at row 2 (heading on row 1)
    arr = rng                                               ' Note column D is Column 1 in the array
  
    For i = 1 To UBound(arr)
        If UCase(arr(i, 10)) = UCase("Brevard") _
            And UCase(arr(i, 11)) = UCase("Sanitary") _
            And (InStr(1, arr(i, 8), "Riser", vbTextCompare) <> 0 _
                Or InStr(1, arr(i, 8), "Cone", vbTextCompare) <> 0) Then
          
              
                If Right(arr(i, 1), 1) <> "J" Then arr(i, 1) = arr(i, 1) & "J"  ' Only add "J" suffix if not already there
                arr(i, 2) = "Yes"
        End If
    Next i
  
    ' write back column D & E values
    rng.Columns(1).Value = Application.Index(arr, 0, 1)
    rng.Columns(2).Value = Application.Index(arr, 0, 2)

End Sub

PS:
You will generally get faster & better answers if you provide sample data in a form that can easily be copied for testing:
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

20220808 VBA Update data using criteria zack8576.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Col1	Col2	Col3	Ref	Y/N	Col6	Col7	Col8	Col9	Col10	Description	Col12	Type	Product Group
2				F145536J	Yes						MH,4'dia,Base,8"w		Brevard	Sanitary
3				F14616J	Yes						MH,4'dia,Riser,8"w		Brevard	Sanitary
4				F14818J	Yes						MH,4'dia,Cone,8"w		Brevard	Sanitary
5				F03957							AGRU LINER - 2mm		Brevard	Sanitary
Data

Thank you Alex, that was super helpful !! Please allow me some time to test this out and I will let you know how it went !