Macro to Replace old data with new data,

[tonywatsonhelp]

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

 

[JGordon11]

Untested:

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]

Here is another macro (no loop) that you can try...

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

 

[JGordon11]

Here is another macro (no loop) that you can try...

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?

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]

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.