Macro to Search for Value in One Column and Append the value in another column

[KlayontKress]

I'm looking for a macro to look for a specific Text String in a column and then append the value in another column. We have a program that exports out to a template in excel to do generate a pricing quote for our customers and We have someone manually doing adding values to the name of the product to reflect it's modifications.

For example, the column I want to search is Column E and the value I want to Append is Column A. I need a macro to search Column E for "ID=15" and then add ID15 to the end of the value in column A. I need the macro to search the entire column of E and do the Modifications as there isn't a set length to the data though it rarely goes past 30 line items.

A picture of the sheet is below. I need to be able to do this with several different values but a basic Macro to do the above will allow me to adapt it to my needs.




[TABLE="width: 522"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Description[/TD]
[TD]Customer Description[/TD]
[TD]Qty[/TD]
[TD]Options[/TD]
[TD]Modifications[/TD]
[/TR]
[TR]
[TD]ACM8[/TD]
[TD][/TD]
[TD]1.00 [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B33 SS1 BUTT[/TD]
[TD][/TD]
[TD]1.00 [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B39 SS1[/TD]
[TD][/TD]
[TD]2.00 [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BTK8 PRT[/TD]
[TD][/TD]
[TD]2.00 [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CM8 BLD[/TD]
[TD][/TD]
[TD]1.00 [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CM8 WD[/TD]
[TD][/TD]
[TD]1.00 [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SHM8 BLD[/TD]
[TD][/TD]
[TD]2.00 [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]UF2[/TD]
[TD][/TD]
[TD]2.00 [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]UF3[/TD]
[TD][/TD]
[TD]1.00 [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]UF342[/TD]
[TD][/TD]
[TD]1.00 [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]UM8 BLD[/TD]
[TD][/TD]
[TD]3.00 [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]UM8 WD[/TD]
[TD][/TD]
[TD]3.00 [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]W3036 BUTT[/TD]
[TD][/TD]
[TD]1.00 [/TD]
[TD]FEDEP;UPBOX[/TD]
[TD]IND12~ID=15[/TD]
[/TR]
[TR]
[TD]W3342 BUTT[/TD]
[TD][/TD]
[TD]1.00 [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]W3624 BUTT[/TD]
[TD][/TD]
[TD]1.00 [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]W3942[/TD]
[TD][/TD]
[TD]2.00 [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]W3036 BUTT[/TD]
[TD][/TD]
[TD]1.00 [/TD]
[TD][/TD]
[TD]IND12~ID=15[/TD]
[/TR]
[TR]
[TD]SM8 WD[/TD]
[TD][/TD]
[TD]1.00 [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col></colgroup>[/TABLE]



Thanks in advance,

 

[igold]

Hi,

So after the value in Column E is found, which value in Column A gets appended. Is it the value in the same row?

 

[KlayontKress]

Hi,

So after the value in Column E is found, which value in Column A gets appended. Is it the value in the same row?

Yes, the value in the same row is getting modified. I should have been more specific.

 

[igold]

How about something like this:

Sub AppendString()
    Dim resp As Variant
    Dim InStng As String
    Dim lRow As Long, i As Long
    Dim rng As Range
    
    lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
    InStng = Application.InputBox("List the values separated by commas:" & _
    vbCrLf & "Search String, Append String", "Values", Type:=2)
    resp = Split(InStng, ",")
    For i = 2 To lRow
        If Cells(i, 5) Like "*" & resp(0) & "*" Then
            Cells(i, 1) = Cells(i, 1) & " " & resp(1)
        End If
    Next
   
End Sub

I hope this helps.

 

[KlayontKress]

How about something like this:

Sub AppendString()
    Dim resp As Variant
    Dim InStng As String
    Dim lRow As Long, i As Long
    Dim rng As Range
    
    lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
    InStng = Application.InputBox("List the values separated by commas:" & _
    vbCrLf & "Search String, Append String", "Values", Type:=2)
    resp = Split(InStng, ",")
    For i = 2 To lRow
        If Cells(i, 5) Like "*" & resp(0) & "*" Then
            Cells(i, 1) = Cells(i, 1) & " " & resp(1)
        End If
    Next
   
End Sub

I hope this helps.

I'm getting an Error at the line that is red.

Also, is there a way to change this so that it doesn't need user intervention? When we export the file into excel to modify it, there is already a series of macros that run to do formatting and I'd like this to happen automatically on import without user intervention.


Sub AppendString()
Dim resp As Variant
Dim InStng As String
Dim lRow As Long, i As Long
Dim rng As Range

lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
InStng = Application.InputBox("List the values separated by commas:" & _
vbCrLf & "Search String, Append String", "Values", Type:=2)
resp = Split(InStng, ",")
For i = 2 To lRow
If Cells(i, 5) Like "*" & resp(0) & "*" Then
Cells(i, 1) = Cells(i, 1) & " " & resp(1)
End If
Next

End Sub

 

[igold]

Two questions:

1) What is the error you are getting?
2) Without user intervention, how is the code going to know what the search terms are and what the append string is?

 

[KlayontKress]

Two questions:

1) What is the error you are getting?
2) Without user intervention, how is the code going to know what the search terms are and what the append string is?

1) The debugger just points a yellow arrow at that line.

2) I'd like to specify in the macro to look for a specific string of text in column E and then add a string of text to the same row in column A.

For example, using the chart I pasted initially, I'd like the macro to look for "ID=15" in Column E and, in the specific case of row 13, add "ID15" to Column A Row 13's Value Changing it from "W3036 Butt" to "W3036 Butt, ID 15".

The text I'll want added to column A will not always be the exact same text being searched for which is why I'd like to be able to specify the nomenclature added to the cell in column A. The reason for this is because we have a difference of nomenclature between what we use and what IT programmed

 

[igold]

As far as the error goes, it was probably a subscript error because you did not enter two strings in the InputBox as instructed.

For example, using the chart I pasted initially, I'd like the macro to look for "ID=15" in Column E and, in the specific case of row 13, add "ID15" to Column A Row 13's Value Changing it from "W3036 Butt" to "W3036 Butt, ID 15".

The text I'll want added to column A will not always be the exact same text being searched for which is why I'd like to be able to specify the nomenclature added to the cell in column A. The reason for this is because we have a difference of nomenclature between what we use and what IT programmed

How is this going to happen without user intervention... Is there going to be a list of search strings and desired nomenclature located somewhere in your sheet..

 

[KlayontKress]

As far as the error goes, it was probably a subscript error because you did not enter two strings in the InputBox as instructed.



How is this going to happen without user intervention... Is there going to be a list of search strings and desired nomenclature located somewhere in your sheet..

No, I was going to define it within the macro. I just need a generic macro to look in column E for a value I specify (within the macro), and add a value to the same row in column A with text I specify (within the macro).

Sub BlahBlahBlahYackitySmackity()

'insert code to define column
'insert code to search defined column for "ID=15" (let's hypothetically there was such a value in E1)
'insert code to change value in column A to (in this specific case): ("A1") & ", ID 15"
End Sub

 

[igold]

You can extract that from my original code:

[COLOR=#333333]lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row[/COLOR]
For i = 2 To lRow
        If Cells(i, 5) Like "*" & [COLOR=#ff0000]resp(0)[/COLOR] & "*" Then
            Cells(i, 1) = Cells(i, 1) & " " & [COLOR=#ff0000]resp(1)[/COLOR]
        End If
Next

This will look down column E and find a wildcard match to resp(0) and then append column A with resp(1). You can replace resp(0) (your search string), and resp(1) (your append string), with your own hardcoded values...