VBA code doesn't replace formulas with values correctly

[NathanA]

This code is to work on two tabs in an excel worksheet.

I'm hoping to use the below code to replace the formulas below row 2 in columns N to AA with the values that would come from having run the formulas that are kept in row 2. So, what I would like in the output is to have formulas in row 2 in columns N to AA (since the headers are in row 1), and all the rows below to be the value of the formula. All the values below row 2 should be different as the formulas are based on an identifying number in column A.

1. However, I don't get the correct output like I would with the formulas. The code changes rows 3 and below to values, however they're not the correct values.
2. Also, in columns J to M, the code copies down whatever is in row 2 to all the other rows below. However, this shouldn't happen as I specified the columns to change in the code.

Any guidance on how to change this would be much appreciated.

Option Explicit


Sub RefreshFormulas()
Dim i As Long
Dim LastRow As Long
Dim Prompt As String
Dim Title As String


    Application.Cursor = xlWait
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False

    With WSNumeracyAnalysis
        DoEvents

        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("N3:AA" & .Rows.Count).ClearContents
        For i = 14 To 27
            Application.StatusBar = "Updating Numeracy Analysis Data: " & i & " of 27"
            DoEvents

            .Range(.Cells(2, i), .Cells(LastRow, i)).Formula = .Cells(2, i).Formula
            .Range(.Cells(3, i), .Cells(LastRow, i)).Value = .Range(.Cells(3, i), .Cells(LastRow, i)).Value
        Next i

    End With


    With WSReadingAnalysis
        DoEvents

        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("N3:AA" & .Rows.Count).ClearContents
        For i = 10 To 27
            Application.StatusBar = "Updating Reading Analysis Data: " & i & " of 27"
            DoEvents

            .Range(.Cells(2, i), .Cells(LastRow, i)).Formula = .Cells(2, i).Formula
            .Range(.Cells(3, i), .Cells(LastRow, i)).Value = .Range(.Cells(3, i), .Cells(LastRow, i)).Value
        Next i


    End With

    Prompt = "The formulas have been refreshed successfully."
    Title = "Process Completed"
    MsgBox Prompt, vbInformation, Title


ExitSub:

    Application.Cursor = xlDefault
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.StatusBar = False


End Sub

 

[StephenCrump]

I don't get the correct output like I would with the formulas. The code changes rows 3 and below to values, however they're not the correct values.

Try commenting out the two lines:

'.Range(.Cells(3, i), .Cells(LastRow, i)).Value = .Range(.Cells(3, i), .Cells(LastRow, i)).Value

Are your formulae producing "correct values", i.e. before you change the formulae to values?

Also, in columns J to M, the code copies down whatever is in row 2 to all the other rows below. However, this shouldn't happen as I specified the columns to change in the code.

Your first loop, For i = 14 to 27 covers columns N-AA

Your second loop, For i = 10 to 27 also includes columns J-M.

 

[NathanA]

Thanks.

The values were originally correct, however, once the formulas were changed to values it seems Excel interpreted the 'blank' cells incorrectly. Since the analysis in some columns involved other columns that had been changed to values, the incorrect values appeared.
I changed that in the formulas, along with the second loop defining columns.