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

allencunningham

New Member
Joined
Feb 16, 2019
Messages
10
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 ... |
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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
 
Upvote 0
Can you use somjethihng like this?

Code:
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
 
Upvote 0
Re: Get the value from one column and put in another column

Hi Allen,
try this code:


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
 
Upvote 0
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
 
Last edited:
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,241
Members
452,622
Latest member
Laura_PinksBTHFT

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
Back
Top