VBA code paste as text not formula problem

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
2,332
Office Version
  1. 365
Platform
  1. Windows
I have code that was running great for more than a year. Now its adding in the formula as text so its not doing the calculation any longer
"=IFERROR((INDEX(Detail!C[-2],MATCH(CMOP!RC[-1],Detail!C[-2],0))),""DELETE"")"

Any help is appreciated

Code:
Sub StripDown()
'
' Macro1 Macro
'
    If Not Evaluate("isref('CMOP'!a1)") Then
                MsgBox "You Did not import the CMOP"
                UserForm1.Hide
                'CMOP.Close False
                Exit Sub
                
    Else


    Dim LstRw As Long
'
    Sheets("CMOP").Select
    
    LstRw = Sheets("CMOP").Range("B" & Rows.Count).End(xlUp).Row
    
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight
    Range("C4").FormulaR1C1 = _
        "=IFERROR((INDEX(Detail!C[-2],MATCH(CMOP!RC[-1],Detail!C[-2],0))),""DELETE"")"
    Range("C4").Select
    Selection.Copy
    
    Range("C4:C" & LstRw).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("C2").Select
    
    Calculate
    
    Dim lr As Long
    
    lr = Cells(Rows.Count, "C").End(xlUp).Row
    
    With Range("C4:C" & lr)
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:="Delete"
            .SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
    
    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
    
    Range("A4").Select
    
    End If
    
    
End Sub
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Replace this
Code:
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight
    Range("C4").FormulaR1C1 = _
        "=IFERROR((INDEX(Detail!C[-2],MATCH(CMOP!RC[-1],Detail!C[-2],0))),""DELETE"")"
    Range("C4").Select
    Selection.Copy
    
    Range("C4:C" & LstRw).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("C2").Select
with
Code:
    Columns("C").Insert Shift:=xlToRight
    Columns("C").NumberFormat = "General"
    Range("C4:C" & LstRw).FormulaR1C1 = _
        "=IFERROR((INDEX(Detail!C[-2],MATCH(CMOP!RC[-1],Detail!C[-2],0))),""DELETE"")"
    Range("C2").Select
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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
Back
Top