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

zack8576

Active Member
Joined
Dec 27, 2021
Messages
255
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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

kevin9999

Well-known Member
Joined
Aug 28, 2020
Messages
1,446
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
 
Upvote 0
Solution

zack8576

Active Member
Joined
Dec 27, 2021
Messages
255
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)")
 
Upvote 0

bebo021999

Well-known Member
Joined
Jul 14, 2011
Messages
2,049
Office Version
  1. 2016
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
 
Upvote 0

kevin9999

Well-known Member
Joined
Aug 28, 2020
Messages
1,446
Office Version
  1. 365
Platform
  1. Windows
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
 
Upvote 0

zack8576

Active Member
Joined
Dec 27, 2021
Messages
255
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 "-" ?
 
Upvote 0

zack8576

Active Member
Joined
Dec 27, 2021
Messages
255
Office Version
  1. 365
Platform
  1. Windows
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!
 
Upvote 0

kevin9999

Well-known Member
Joined
Aug 28, 2020
Messages
1,446
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.
 
Upvote 0

bebo021999

Well-known Member
Joined
Jul 14, 2011
Messages
2,049
Office Version
  1. 2016
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.
 
Upvote 0

zack8576

Active Member
Joined
Dec 27, 2021
Messages
255
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
 
Upvote 0

Forum statistics

Threads
1,186,808
Messages
5,959,932
Members
438,454
Latest member
leopedrini

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