Problems Pasting Values

**Dean81** · 01-24-2012, 09:05 AM

Hello,

I'm not really sure how to describe my problem and as such can't think what to search for. Here's the problem; I have a macro that pastes values into column B and then in column A puts the formula =IF(B2=D2,"","Change Detected")

The formula in column A doesn't appear to pick up that there is anything in column B unless I select cell B2, click in the formula bar and hit enter and repeat this all the way down column B.

Does anyone know why this is and how I can stop it happening?

Thanks,

Dean

**swoop99** · 01-24-2012, 09:10 AM

Cut and paste your macro in here, need to see it.

**Dean81** · 01-24-2012, 09:15 AM

Range("B2").Select
    Do
    ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[1],"":00"")"
    ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, 4))
    Columns("B:B").Select
    Selection.Copy
    Columns("B:B").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Range("A2").Select
    Do
    ActiveCell.FormulaR1C1 = "=IF(RC[1]=RC[3],"""",""Change Detected"")"
    ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, 5))

**swoop99** · 01-25-2012, 01:56 PM

Try this instead.


For DataSweep = 2 to Range("F65536").End(xlUp).Row
     Cells(datasweep, 2).formulaR1C1 = "=CONCATENATE(RC[1],"":00"")"
     Cells(datasweep, 1).formulaR1C1 = "=IF(RC[1]=RC[3],"""",""Change Detected"")"
next datasweep

Columns("B:B").Select
    Selection.Copy
    Columns("B:B").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    range("a1").select