Macro to Replace old data with new data,

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,931
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi Everyone,

Every two months I get a list of new data the I need to replace some of my old data with.

I have figures in Range C2:C700 and Products in Range E2:E700

In Column J2:J700 I have either "No Update", Blank, or New Data,
I'd like a macro that when run could go through each row and if J does not contain either "No Update", Blank, Over write Column C with whats in column J and column E with whats in Column K

Please help if you can.

Thanks

Tony
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

JGordon11

Active Member
Joined
Jan 18, 2021
Messages
297
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Untested:

VBA Code:
Sub Update()
    Const lr = 700
    Dim c, e, j, k, i As Long
    c = Range("C2:C" & lr)
    e = Range("E2:E" & lr)
    j = Range("J2:J" & lr)
    k = Range("K2:K" & lr)
    For i = 1 To lr - 1
        If j(i, 1) <> "No Update" And j(i, 1) <> "" Then
            c(i, 1) = j(i, 1)
            e(i, 1) = k(i, 1)
        End If
    Next
    Range("C2:C" & lr).Value = c
    Range("E2:E" & lr).Value = e
End Sub
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,556
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Here is another macro (no loop) that you can try...
VBA Code:
Sub Replacements()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "J").End(xlUp).Row
  Range("C2:C" & LastRow) = Evaluate(Replace("IF((J2:J@<>"""")*(J2:J@<>""No Update""),J2:J@,IF(C2:C@="""","""",C2:C@))", "@", LastRow))
  Range("E2:E" & LastRow) = Evaluate(Replace("IF((J2:J@<>"""")*(J2:J@<>""No Update""),K2:K@,IF(E2:E@="""","""",E2:E@))", "@", LastRow))
End Sub
 
Last edited:
Solution

JGordon11

Active Member
Joined
Jan 18, 2021
Messages
297
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Here is another macro (no loop) that you can try...
VBA Code:
Sub Replacements()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "J").End(xlUp).Row
  Range("C2:C" & LastRow) = Evaluate(Replace("IF((J2:J@<>"""")*(J2:J@<>""No Update""),J2:J@,IF(C2:C@="""","""",C2:C@))", "@", LastRow))
  Range("E2:E" & LastRow) = Evaluate(Replace("IF((J2:J@<>"""")*(J2:J@<>""No Update""),K2:K@,IF(E2:E@="""","""",E2:E@))", "@", LastRow))
End Sub
Hi Rick - I like the no loop solution. But why the IF(C2... and IF)E2... parts? Wouldn't C2:C@ and E2:E@ do the same thing?

VBA Code:
Sub Replacements()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "J").End(xlUp).Row
  Range("C2:C" & LastRow) = Evaluate(Replace("IF((J2:J@<>"""")*(J2:J@<>""No Update""),J2:J@,C2:C@)", "@", LastRow))
  Range("E2:E" & LastRow) = Evaluate(Replace("IF((J2:J@<>"""")*(J2:J@<>""No Update""),K2:K@,E2:E@)", "@", LastRow))
End Sub
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,556
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
It is a protection against cells in Column C and E being blank... if you had a blank in either of those columns and Column J was blank or "No Update", that blank in Column C or E would become zero instead of remaining blank.
 

Forum statistics

Threads
1,148,249
Messages
5,745,627
Members
423,964
Latest member
Dustin M

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