VBA code doesn't replace formulas with values correctly

NathanA

New Member
Joined
Jan 18, 2017
Messages
34
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.
Code:
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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,048
Office Version
  1. 365
Platform
  1. Windows
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:

Code:
'.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

New Member
Joined
Jan 18, 2017
Messages
34
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,320
Messages
5,600,935
Members
414,417
Latest member
Nobu

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