(VBA) Search and find certain text in a column, add the number from the neighboring column to the end of the text

[zack8576]

Hello, I am new to this forum, and new to VBA.
I need to write a VBA script that can search column K and find text "C BOX(6"WALL), and add the number that is located in column L on the same row to the end of the text.
How can I do this? Any help is greatly appreciated !

 

[kevin9999]

Try this

Sub Zack()
    Dim ws As Worksheet, lr As Long, c As Range
    Set ws = ActiveSheet
    lr = ws.Cells(Rows.Count, 11).End(xlUp).Row
    
    For Each c In ws.Range("K2:K" & lr)
        If c = "C BOX (6""" & " WALL)" Then c = c & " " & c.Offset(, 1)
    Next c
End Sub

 

[zack8576]

Try this

Sub Zack()
    Dim ws As Worksheet, lr As Long, c As Range
    Set ws = ActiveSheet
    lr = ws.Cells(Rows.Count, 11).End(xlUp).Row
   
    For Each c In ws.Range("K2:K" & lr)
        If c = "C BOX (6""" & " WALL)" Then c = c & " " & c.Offset(, 1)
    Next c
End Sub

Thanks Kevin, that worked like a charm !!
I will need to study each component of these lines of code and understand how every bit works..
Also, what if I want to take this one step further, lets say I need to search and find a list of texts in column K and modify them the same way we just did.
see below for an example list:
C BOX(6" WALL)
D BOX(6" WALL)
SAN MH(5" WALL)
MH 72" DIA(8" WALL)

instead of defining c As Range, can I use
findList = Array("C BOX(6" WALL)", "D BOX(6" WALL)", "SAN MH(5" WALL)", "MH 72" DIA(8" WALL)")

 

[bebo021999]

One way: combine all condition into single string:
FindString = "-" & "C BOX(6" WALL)" & "-" & "D BOX(6" WALL)" & "-" & "SAN MH(5" WALL)" & "-" & "MH 72" DIA(8" WALL)" & "-"
then search "c" in string:
If instr(1,FindString,"-" & c & "-") > 0 then

 

[kevin9999]

Another way is to use Or to do that - either way will work, although @bebo021999 's method might be a better way to go given the double quotation trickiness...

Option Explicit
Sub Zack()
    Dim ws As Worksheet, lr As Long, c As Range
    Set ws = ActiveSheet
    lr = ws.Cells(Rows.Count, 11).End(xlUp).Row
    
    For Each c In ws.Range("K2:K" & lr)
        If c = "C BOX (6""" & " WALL)" Or _
        c = "D BOX (6""" & " WALL)" Or _
        c = "SAN MH(5""" & " WALL)" Or _
        c = "MH 72""" & " DIA(8""" & " WALL)" Then
        c = c & " " & c.Offset(, 1)
        End If
    Next c
End Sub

 

[zack8576]

One way: combine all condition into single string:
FindString = "-" & "C BOX(6" WALL)" & "-" & "D BOX(6" WALL)" & "-" & "SAN MH(5" WALL)" & "-" & "MH 72" DIA(8" WALL)" & "-"
then search "c" in string:
If instr(1,FindString,"-" & c & "-") > 0 then

Bebo, thanks for your help. May I ask, what is the exact function of the "-" ?

 

[zack8576]

Another way is to use Or to do that - either way will work, although @bebo021999 's method might be a better way to go given the double quotation trickiness...

Option Explicit
Sub Zack()
    Dim ws As Worksheet, lr As Long, c As Range
    Set ws = ActiveSheet
    lr = ws.Cells(Rows.Count, 11).End(xlUp).Row
   
    For Each c In ws.Range("K2:K" & lr)
        If c = "C BOX (6""" & " WALL)" Or _
        c = "D BOX (6""" & " WALL)" Or _
        c = "SAN MH(5""" & " WALL)" Or _
        c = "MH 72""" & " DIA(8""" & " WALL)" Then
        c = c & " " & c.Offset(, 1)
        End If
    Next c
End Sub

Kevin, thank you so much for the answer. what does Option explicit do?

thanks!

 

[kevin9999]

Kevin, thank you so much for the answer. what does Option explicit do?

thanks!

Option Explicit is a habit you should get into - it forces you to declare all variables you use throughout your code. This helps avoid many errors. To demonstrate, using the code in post #5, remove the

c as Range

bit and try running it again.

 

[bebo021999]

Bebo, thanks for your help. May I ask, what is the exact function of the "-" ?

When combine strings, it helps to avoid possition combination like this: "learn" and "task" to become "learntask". It could be combination of "learnt" and "ask". So, add a separate symbol to keep original string.

 

[zack8576]

When combine strings, it helps to avoid possition combination like this: "learn" and "task" to become "learntask". It could be combination of "learnt" and "ask". So, add a separate symbol to keep original string.

Thanks Bebo. I am writing this down in my notebook