Get the contents of column A and put it in Column B overwiting a tag

[allencunningham]

I am working with just 2 columns. I am writing a macro to get the value from column A
and put it in column B, overwriting the tag style, so that row 1 appears like the following:

The Regency Straight Back is a traditional .....,
and do this for all rows

Column A | Column B |
Regency Straight Back | The style is a traditional .... |
Modern Alba | This style is our latest ...... |
Classical Wide back | Our new style is finally available ... |

 

[allencunningham]

Get the value from one column and put in another column

To simplify, I am working with just 2 columns. I am writing a macro to get the value from column A like Regency Strait Back and put it in column B, overwriting the placeholder XXX, so that row one appears like the following:

The Regency Straight Back is a traditional .......
and do this for all rows

Column A | Column B |
Regency Straight Back | The XXX is a traditional .... |
Modern Alba | This XXX is our latest ...... |
Classical Wide back | Our new XXX is finally available ... |
I can easily do this by doing a Control H with replacing the XXX with what’s in column A however I have thousands of rows I need to work with.
Is there any way to automate this?

Thanks, Allen

 

[JLGWhiz]

Can you use somjethihng like this?

Sub t()
Dim c As Range, txt As String
With ActiveSheet
    For Each c In .Range("A2", .Cells(Rows.Count, 1).End(xlUp))
        If InStr(c.Offset(, 1), "style") > 0 Then
            c = c.Value & " " & Mid(c.Offset(, 1), InStr(c.Offset(, 1), "style") + 5)
            c.Offset(, 1).ClearContents
        End If
    Next
End With
End Sub

 

[Sequoyah]

Re: Get the value from one column and put in another column

Hi Allen,
try this code:

Sub ReplaceXXX()
    
    Dim i As Integer
    Dim LastRow As Integer
    
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = 1 To LastRow
        
        Range("B" & i).Replace What:="XXX", Replacement:=Range("A" & i).Value
        
        
    Next
    
End Sub

 

[allencunningham]

I think something like this will work. This example is inserting text into column one rather than column two

It needs a slight modification as follows: (1) needs to start in A1
(2) It is pulling out the last 8 chars from column b after the word style and putting them at the end of what's in column a

What we want to do is pull the value in column a and put it in b where the word style is

 

[jtakw]

Re: Get the value from one column and put in another column

Seems awfully similar to this post:

https://www.mrexcel.com/forum/excel...tents-column-put-column-b-overwiting-tag.html

 

[allencunningham]

Re: Get the value from one column and put in another column

Perfect, thank you so much

 

[Fluff]

Re: Get the value from one column and put in another column

@allencunningham
Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. (rule 12 here: Forum Rules).
I have merged both threads

 

[allencunningham]

Re: Get the value from one column and put in another column

Sequoyah - Thank you very much, that worked!

 

[allencunningham]

Re: Get the value from one column and put in another column

Hello Sequoyah - I tested it in my excel file and found one complication, column B is actually a longer field with about 100 words of descriptive text in it, and the 'XXX' is buried in the middle of it somewhere.
Column A has just 2 or 3 words in it

The program works if there is only a few words in column a and a few in column b

Column A | Column B |
Regency Straight Back | The XXX is a traditional .... |
Modern Alba | This XXX is our latest ...... |
Classical Wide back | Our new XXX is finally available ... |

See any way to modify it?