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

zack8576

New Member
Joined
Dec 27, 2021
Messages
41
Office Version
  1. 365
Platform
  1. Windows
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 !

Untitled.png
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

kevin9999

Board Regular
Joined
Aug 28, 2020
Messages
129
Office Version
  1. 365
Platform
  1. Windows
Try this

VBA Code:
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
 
Solution

zack8576

New Member
Joined
Dec 27, 2021
Messages
41
Office Version
  1. 365
Platform
  1. Windows
Try this

VBA Code:
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

Active Member
Joined
Jul 14, 2011
Messages
443
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

Board Regular
Joined
Aug 28, 2020
Messages
129
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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...

VBA Code:
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

New Member
Joined
Dec 27, 2021
Messages
41
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 27, 2021
Messages
41
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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...

VBA Code:
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

Board Regular
Joined
Aug 28, 2020
Messages
129
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
c as Range
bit and try running it again.
 

bebo021999

Active Member
Joined
Jul 14, 2011
Messages
443
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

New Member
Joined
Dec 27, 2021
Messages
41
Office Version
  1. 365
Platform
  1. Windows
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
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,491
Messages
5,764,681
Members
425,229
Latest member
Rashid mahmood

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top