Replace Word in Cell with Value from Another Cell

tomgrandy · May 10, 2024

I'm new to VBA and am trying to replace a word found in L3 (AUCTION_DATE) with the value from H3 (Date Lot Sold - there are 70,000 entries and not all are 02-04-23) and then have this repeated throughout the worksheet for all items in Row L that have the words "AUCTION_DATE".

Basically replace the words "AUCTION_DATE" in each row of Column L with the value in the corresponding cell value in Column H (for each row).

Spent the best part of two hours trying to search for a site that had the solution, but none of the Find and Replace VBA codes would pull from another cell.

Any help greatly appreciated!!

Screenshot 2024-05-10 at 12.13.48 PM.png

Joe4 · May 10, 2024

Welcome to the Board!

This can be done pretty easily with a basic formula or with VBA.
Would a basic formula work for you, or does it definitely have to be VBA?

Is it safe to assume that every row would look at the column H value in that particular row (all rows aren't looking at H3, are they)?

Are the values in column H real date entries with a format of "mm-dd-yy", or are they text entries?

Joe4 · May 10, 2024

Assuming that each row is looking at the value in column H of that row for the replacement, this VBA code should do what you want (regardless of whether the entries in column H are valid dates or "dates entered as text").

VBA Code:

Sub MyReplaceMacro()

    Dim lr As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Find last row with data in column L
    lr = Cells(Rows.Count, "L").End(xlUp).Row
    
'   Loop through all rows starting with row 2
    For r = 2 To lr
'       Check to see if column L value has "AUCTION_DATE" in it
        If InStr(1, Cells(r, "L"), "AUCTION_DATE") > 0 Then
'           Replace value with column H
            Cells(r, "L").Value = Replace(Cells(r, "L").Value, "AUCTION_DATE", Format(Cells(r, "H"), "mm-dd-yy"))
        End If
    Next r
    
    Application.ScreenUpdating = True
    
    MsgBox "Macro complete!"
    
End Sub

JamesCanale · May 10, 2024

formula instead of vba

MrExcelPlayground22.xlsx

H

I

J

K

L

M

3

02-04-23

site/default/blah/blah/AUCTION_DATE/1.jpg

site/default/blah/blah/02-04-23/1.jpg

4

02-05-23

site/default/blah/blah/AUCTION_DATE/2.jpg

site/default/blah/blah/02-05-23/2.jpg

5

02-06-23

site/default/blah/blah/AUCTION_DATE/3.jpg

site/default/blah/blah/02-06-23/3.jpg

6

02-07-23

site/default/blah/blah/AUCTION_DATE/4.jpg

site/default/blah/blah/02-07-23/4.jpg

7

02-08-23

site/default/blah/blah/AUCTION_DATE/5.jpg

site/default/blah/blah/02-08-23/5.jpg

8

02-09-23

site/default/blah/blah/AUCTION_DATE/6.jpg

site/default/blah/blah/02-09-23/6.jpg

9

02-10-23

site/default/blah/blah/AUCTION_DATE/7.jpg

site/default/blah/blah/02-10-23/7.jpg

10

02-11-23

site/default/blah/blah/AUCTION_DATE/8.jpg

site/default/blah/blah/02-11-23/8.jpg

11

02-12-23

site/default/blah/blah/AUCTION_DATE/9.jpg

site/default/blah/blah/02-12-23/9.jpg

12

02-13-23

site/default/blah/blah/AUCTION_DATE/10.jpg

site/default/blah/blah/02-13-23/10.jpg

13

02-14-23

site/default/blah/blah/AUCTION_DATE/11.jpg

site/default/blah/blah/02-14-23/11.jpg

14

02-15-23

site/default/blah/blah/AUCTION_DATE/12.jpg

site/default/blah/blah/02-15-23/12.jpg

15

02-16-23

site/default/blah/blah/AUCTION_DATE/13.jpg

site/default/blah/blah/02-16-23/13.jpg

16

02-17-23

site/default/blah/blah/AUCTION_DATE/14.jpg

site/default/blah/blah/02-17-23/14.jpg

17

02-18-23

site/default/blah/blah/AUCTION_DATE/15.jpg

site/default/blah/blah/02-18-23/15.jpg

18

02-19-23

site/default/blah/blah/AUCTION_DATE/16.jpg

site/default/blah/blah/02-19-23/16.jpg

19

02-20-23

site/default/blah/blah/AUCTION_DATE/17.jpg

site/default/blah/blah/02-20-23/17.jpg

20

02-21-23

site/default/blah/blah/AUCTION_DATE/18.jpg

site/default/blah/blah/02-21-23/18.jpg

21

02-22-23

site/default/blah/blah/AUCTION_DATE/19.jpg

site/default/blah/blah/02-22-23/19.jpg

22

02-23-23

site/default/blah/blah/AUCTION_DATE/20.jpg

site/default/blah/blah/02-23-23/20.jpg

23

02-24-23

site/default/blah/blah/AUCTION_DATE/21.jpg

site/default/blah/blah/02-24-23/21.jpg

Sheet26

Cell Formulas
Range	Formula
M3:M23	M3	=LEFT(L3:L23,SEARCH("AUCTION_DATE",L3:L23)-1)&TEXT(H3:H23,"mm-dd-yy")&RIGHT(L3:L23,LEN(L3:L23)-SEARCH("AUCTION_DATE",L3:L23)-LEN("AUCTION_DATE")+1)
Dynamic array formulas.

tomgrandy · May 10, 2024

Dude! That VBA script worked like a charm!!

I'm creating these for a group that simply needs a button to run the script so keeping the VBA out of their sight.

I so appreciate your quick response - YOU ROCK!!

mumps · May 10, 2024

Another approach:

VBA Code:

Sub ReplaceText()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long, arr() As Variant, x As Long, lRow As Long
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    v = Range("H2", Range("H" & Rows.Count).End(xlUp)).Resize(, 5).Value
    ReDim arr(1 To lRow - 1)
    For i = LBound(v) To UBound(v)
        If InStr(v(i, 5), "AUCTION_DATE") > 0 Then
            x = x + 1
            arr(x) = Replace(v(i, 5), "AUCTION_DATE", v(i, 1), 1)
        Else
            x = x + 1
            arr(x) = v(i, 5)
        End If
    Next i
    Range("L2").Resize(lRow - 1) = Application.Transpose(arr)
    Application.ScreenUpdating = True
End Sub

The code uses an array so it should be pretty quick. It assumes your data starts in cell H2.

Joe4 · May 10, 2024

tomgrandy said:
Dude! That VBA script worked like a charm!!

I'm creating these for a group that simply needs a button to run the script so keeping the VBA out of their sight.

I so appreciate your quick response - YOU ROCK!!

I figured it might be a piece of a larger project.
Glad it worked out for you!

tomgrandy · May 14, 2024

mumps said:

Another approach:

VBA Code:

Sub ReplaceText()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long, arr() As Variant, x As Long, lRow As Long
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    v = Range("H2", Range("H" & Rows.Count).End(xlUp)).Resize(, 5).Value
    ReDim arr(1 To lRow - 1)
    For i = LBound(v) To UBound(v)
        If InStr(v(i, 5), "AUCTION_DATE") > 0 Then
            x = x + 1
            arr(x) = Replace(v(i, 5), "AUCTION_DATE", v(i, 1), 1)
        Else
            x = x + 1
            arr(x) = v(i, 5)
        End If
    Next i
    Range("L2").Resize(lRow - 1) = Application.Transpose(arr)
    Application.ScreenUpdating = True
End Sub

The code uses an array so it should be pretty quick. It assumes your data starts in cell H2.

@mumps - Thanks for the array approach! Makes me wonder if an Array would be a better method for running though the following which I had to split into three different vba's because it exceeded the number of calls that could be made. What is happening is an entire vocabulary of terms are being compared to the description cell to see if any are there and then defines the term to be printed into another cell for a Keyword to be assigned. If an Array would speed up the process (takes about 20 seconds for each VBA to run) then that would be a huge timesaver!:

VBA Code:

Option Compare Text

Sub KeywordFlakedArtifacts_A_K()

Application.ScreenUpdating = False

Dim cell As Range

        For Each cell In Range("c2:c70000")
        
        If InStr(cell.Value, "ABBEY") <> 0 Then
            cell.Offset(0, 7).Value = "POINT"
        End If
        
        If InStr(cell.Value, "ACATITA") <> 0 Then
            cell.Offset(0, 7).Value = "POINT"
        End If
        
        If InStr(cell.Value, "ADDISON MICRO-DRILL") <> 0 Then
            cell.Offset(0, 7).Value = "POINT"
        End If

        Next cell
    
End Sub

tomgrandy · May 14, 2024

JamesCanale said:
formula instead of vba

MrExcelPlayground22.xlsx
H I J K L M
3 02-04-23 site/default/blah/blah/AUCTION_DATE/1.jpg site/default/blah/blah/02-04-23/1.jpg
4 02-05-23 site/default/blah/blah/AUCTION_DATE/2.jpg site/default/blah/blah/02-05-23/2.jpg
5 02-06-23 site/default/blah/blah/AUCTION_DATE/3.jpg site/default/blah/blah/02-06-23/3.jpg
6 02-07-23 site/default/blah/blah/AUCTION_DATE/4.jpg site/default/blah/blah/02-07-23/4.jpg
7 02-08-23 site/default/blah/blah/AUCTION_DATE/5.jpg site/default/blah/blah/02-08-23/5.jpg
8 02-09-23 site/default/blah/blah/AUCTION_DATE/6.jpg site/default/blah/blah/02-09-23/6.jpg
9 02-10-23 site/default/blah/blah/AUCTION_DATE/7.jpg site/default/blah/blah/02-10-23/7.jpg
10 02-11-23 site/default/blah/blah/AUCTION_DATE/8.jpg site/default/blah/blah/02-11-23/8.jpg
11 02-12-23 site/default/blah/blah/AUCTION_DATE/9.jpg site/default/blah/blah/02-12-23/9.jpg
12 02-13-23 site/default/blah/blah/AUCTION_DATE/10.jpg site/default/blah/blah/02-13-23/10.jpg
13 02-14-23 site/default/blah/blah/AUCTION_DATE/11.jpg site/default/blah/blah/02-14-23/11.jpg
14 02-15-23 site/default/blah/blah/AUCTION_DATE/12.jpg site/default/blah/blah/02-15-23/12.jpg
15 02-16-23 site/default/blah/blah/AUCTION_DATE/13.jpg site/default/blah/blah/02-16-23/13.jpg
16 02-17-23 site/default/blah/blah/AUCTION_DATE/14.jpg site/default/blah/blah/02-17-23/14.jpg
17 02-18-23 site/default/blah/blah/AUCTION_DATE/15.jpg site/default/blah/blah/02-18-23/15.jpg
18 02-19-23 site/default/blah/blah/AUCTION_DATE/16.jpg site/default/blah/blah/02-19-23/16.jpg
19 02-20-23 site/default/blah/blah/AUCTION_DATE/17.jpg site/default/blah/blah/02-20-23/17.jpg
20 02-21-23 site/default/blah/blah/AUCTION_DATE/18.jpg site/default/blah/blah/02-21-23/18.jpg
21 02-22-23 site/default/blah/blah/AUCTION_DATE/19.jpg site/default/blah/blah/02-22-23/19.jpg
22 02-23-23 site/default/blah/blah/AUCTION_DATE/20.jpg site/default/blah/blah/02-23-23/20.jpg
23 02-24-23 site/default/blah/blah/AUCTION_DATE/21.jpg site/default/blah/blah/02-24-23/21.jpg
Sheet26
Cell Formulas
Range Formula
M3:M23 M3 =LEFT(L3:L23,SEARCH("AUCTION_DATE",L3:L23)-1)&TEXT(H3:H23,"mm-dd-yy")&RIGHT(L3:L23,LEN(L3:L23)-SEARCH("AUCTION_DATE",L3:L23)-LEN("AUCTION_DATE")+1)
Dynamic array formulas.

Thanks James!!

I'm trying to keep it all within VBA so I can create buttons for the end user to run through the macros.

Didn't think I could assign a formula to a button like I could a macro. OR am I wrong?

If so, there is a find and replace that I would like to run a formula on with a button.

mumps · May 14, 2024

This macro assumes that you have three strings to find ("ABBEY", "ACATITA", "ADDISON MICRO-DRILL").

VBA Code:

Sub FilterData()
    Application.ScreenUpdating = False
    Dim v As Variant, v2 As Variant, i As Long, arr() As Variant, x As Long, lRow As Long
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    v = Range("C2", Range("C" & Rows.Count).End(xlUp)).Value
    v2 = Array("ABBEY", "ACATITA", "ADDISON MICRO-DRILL")
    For i = LBound(v2) To UBound(v2)
        If Not IsError(Application.Match(v2(i), v, 0)) Then
            x = x + 1
            ReDim Preserve arr(1 To x)
            arr(x) = v2(i)
        End If
    Next i
    Range("C1").AutoFilter Field:=1, Criteria1:=arr, Operator:=xlFilterValues
    Range("J2:J" & lRow).SpecialCells(xlVisible) = "POINT"
    Range("C1").AutoFilter
    Application.ScreenUpdating = True
End Sub

Replace Word in Cell with Value from Another Cell

tomgrandy

New Member

Excel Facts

Joe4

MrExcel MVP, Junior Admin

Joe4

MrExcel MVP, Junior Admin

JamesCanale

Well-known Member

tomgrandy

New Member

mumps

Well-known Member

Joe4

MrExcel MVP, Junior Admin

tomgrandy

New Member

tomgrandy

New Member

mumps

Well-known Member

Similar threads

Forum statistics

Share this page

Replace Word in Cell with Value from Another Cell

New Member

Excel Facts

MrExcel MVP, Junior Admin

MrExcel MVP, Junior Admin

Well-known Member

New Member

Well-known Member

MrExcel MVP, Junior Admin

New Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock