VBA Copy and Paste Cell Value Only- It keep returning #N/A

[fluffyvampirekitten]

I am having problems with the code listed below.

I'm trying to use indexmatch with another workbook to get the values and save the value as cell values .
But it returns a value of #N/A.
I want a to save it as paste cell value only and not formula

Can anyone suggest a solution? or please tell me whats went wrong with the code .

Private Sub Unsuccessful()
'Update Column S and T
'S = Active Ext ID , T = Inactive Ext ID
Dim MaxRowNum As Long
Sheets("SimPat").Select
'Set up an Error handler
On Error GoTo errorFound
Err.Clear
On Error GoTo 0
'Speed up the formula
With Application
        .ScreenUpdating = False
        .Calculation = xlManual
End With
    
'Vlookup/IndexMatch Active Ext ID
Range("S2").FormulaR1C1 = _
      "=INDEX('[PatientMerge.xls]2015'!C10,MATCH(C[-16],'[PatientMerge.xls]2015'!C10,0))"
'Vlookup/IndexMatch Inactive Ext ID
Range("T2").FormulaR1C1 = _
      "=INDEX('[PatientMerge.xls]2015'!C11,MATCH(C[-17],'[PatientMerge.xls]2015'!C11,0))"
' Locate last filled row in column S (this instead of the loop)
    MaxRowNum = Range("A" & Rows.Count).End(xlUp).Row
    
'Autofill the rest of the rows
    Range("S2:T2").Select
    Selection.AutoFill Destination:=Range("S2:T" & MaxRowNum), Type:=xlFillDefault
    
    
    'Column S and T Autofit
    
    Columns("S:T").Select
    Columns("S:T").EntireColumn.AutoFit
    
    'Copy and Paste data as value
    'Columns("S:T").Select
    '=================================================
    ' Errors on Paste formulae/code
    '=================================================
    Range("S2:T" & MaxRowNum).Select
    Selection.Copy
    
    Worksheets("Simpat").Range("S2:T" & MaxRowNum).PasteSpecial xlPasteValues
    
    'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
    
    
    
    Application.CutCopyMode = False
    Range("S2").Select
'Close the speed up
With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
End With
'Close the error Handler
Exit Sub
errorFound:
If Err.Number > 0 Then MsgBox Err.Description, vbCritical, "Error#: & Err.Number"
Err.Clear
End Sub

 

[ronakv]

A few things that could be a problem & can only be debugged if we have the excel you are working with (or a sample)
1. After you fill the cells in the orifinal workbook, does Index Match calculate values or is it #N/A in source
2. Is your Destination S2:Tx range blank when you paste? may be there already exists #N/A and the pasting is not happening at all.
3. Does anything get pasted? You might have to activate the Worksheets("Simpat") before pasting.

Try adding the following before pasting and you can change it to automatic after cut copy mode = false.

Application.Calculation = xlCalculationManual
' Code to copy paste
Application.CutCopyMode = False
Application.Calculation = xlCalculationAutomatic

 

[fluffyvampirekitten]

Answer in bold

A few things that could be a problem & can only be debugged if we have the excel you are working with (or a sample)

How do i attach the Sample files . Sorry I could't provide you the original files because of the personal data and theres really alot of data .
1. After you fill the cells in the orifinal workbook, does Index Match calculate values or is it #N/A in source

-It calculate the values , the data were displayed but after i use the copy/paste code , all the cells values displayed #N/A
2. Is your Destination S2:Tx range blank when you paste? may be there already exists #N/A and the pasting is not happening at all.
3. Does anything get pasted? You might have to activate the Worksheets("Simpat") before pasting.
Nothing get pasted

Try adding the following before pasting and you can change it to automatic after cut copy mode = false.

Application.Calculation = xlCalculationManual
' Code to copy paste
Application.CutCopyMode = False
Application.Calculation = xlCalculationAutomatic

 

[ronakv]

I have raised some errors in your code.
Please follow a debug mode and let us know what you see and where does it fail.

If you cannot provide the data, please replace it with some sample values and help us with 10 rows of file to understand what the issue is.

Private Sub Unsuccessful()
'Update Column S and T
'S = Active Ext ID , T = Inactive Ext ID
Dim MaxRowNum As Long
Sheets("SimPat").Select
'Set up an Error handler
On Error GoTo errorFound
Err.Clear
On Error GoTo 0
'Speed up the formula
With Application
        .ScreenUpdating = False
        .Calculation = xlManual
End With
    
'Vlookup/IndexMatch Active Ext ID

[B][COLOR=#ff0000][/COLOR][/B][COLOR=#ff0000]HOW IS THIS (bold below) NOT IN THE FORMAT OF [/COLOR][B][COLOR=#ff0000]INDEX([U]ARRAY[/U],ROW). [/COLOR][/B][COLOR=#ff0000]your array seems just a cell !![/COLOR]
Range("S2").FormulaR1C1 = _
      "=INDEX([B]'[PatientMerge.xls]2015'!C10[/B],MATCH(C[-16],'[PatientMerge.xls]2015'!C10,0))"
'Vlookup/IndexMatch Inactive Ext ID
Range("T2").FormulaR1C1 = _
      "=INDEX('[PatientMerge.xls]2015'!C11,MATCH(C[-17],'[PatientMerge.xls]2015'!C11,0))"
' Locate last filled row [B]in column S[/B] (this instead of the loop)
    MaxRowNum = Range("[B]A[/B]" & Rows.Count).End(xlUp).Row
[COLOR=#ff0000][/COLOR][COLOR=#ff0000]You willing to find the last row in [B]Column S[/B] and you are searching for the last row in [B]Column A[/B][/COLOR]
    
'Autofill the rest of the rows
    Range("S2:T2").Select
    Selection.AutoFill Destination:=Range("S2:T" & MaxRowNum), Type:=xlFillDefault
    
[COLOR=#ff0000]After Autofill, there is no calculation done !![/COLOR]    

    'Column S and T Autofit
    
    Columns("S:T").Select
    Columns("S:T").EntireColumn.AutoFit
    
    'Copy and Paste data as value
    'Columns("S:T").Select
    '=================================================
    ' Errors on Paste formulae/code
    '=================================================
    Range("S2:T" & MaxRowNum).Select
    Selection.Copy
    
[COLOR=#ff0000]Before Paste Activate the sheet. Or if i read it correctly you trying to paste data on the same same place where you are applying the formula[/COLOR]

    Worksheets("Simpat").Range("S2:T" & MaxRowNum).PasteSpecial xlPasteValues
    
    'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
    
    
    
    Application.CutCopyMode = False
    Range("S2").Select
'Close the speed up
With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
End With
'Close the error Handler
Exit Sub
errorFound:
If Err.Number > 0 Then MsgBox Err.Description, vbCritical, "Error#: & Err.Number"
Err.Clear
End Sub

 

[fluffyvampirekitten]

I have raised some errors in your code.
Please follow a debug mode and let us know what you see and where does it fail.

If you cannot provide the data, please replace it with some sample values and help us with 10 rows of file to understand what the issue is.

How can i provide you the data ? I don't see any attachments button ?

 

[fluffyvampirekitten]

I'm so sorry , still new with excel vba . Hope you understands and thanks for trying to help me :D

Private Sub Unsuccessful()
'Update Column S and T
'S = Active Ext ID , T = Inactive Ext ID
Dim MaxRowNum As Long
Sheets("SimPat").Select
'Set up an Error handler
On Error GoTo errorFound
Err.Clear
On Error GoTo 0
'Speed up the formula
With Application
        .ScreenUpdating = False
        .Calculation = xlManual
End With
    
'Vlookup/IndexMatch Active Ext ID

[COLOR=#ff0000]HOW IS THIS (bold below) NOT IN THE FORMAT OF [/COLOR][B][COLOR=#ff0000]INDEX([U]ARRAY[/U],ROW). [/COLOR][/B][COLOR=#ff0000]your array seems just a cell !!
[/COLOR][COLOR=#0000cd]It was generated thru macro recorder. If i change to array, the array value will be this (R1:R1048576) I think i tried using this value and I encounter some problem . So I used the macro recorder instead to generate the below code. 
[/COLOR]
Range("S2").FormulaR1C1 = _
      "=INDEX([B]'[PatientMerge.xls]2015'!C10[/B],MATCH(C[-16],'[PatientMerge.xls]2015'!C10,0))"

'Vlookup/IndexMatch Inactive Ext ID
Range("T2").FormulaR1C1 = _
      "=INDEX('[PatientMerge.xls]2015'!C11,MATCH(C[-17],'[PatientMerge.xls]2015'!C11,0))"
' Locate last filled row [B]in column S[/B] (this instead of the loop)
    MaxRowNum = Range("[B]A[/B]" & Rows.Count).End(xlUp).Row
[COLOR=#ff0000]You willing to find the last row in [B]Column S[/B] and you are searching for the last row in [B]Column A[/B][/COLOR]
    [COLOR=#0000cd]So i should search for the last row in Column S instead ?[/COLOR]
'Autofill the rest of the rows
    Range("S2:T2").Select
    Selection.AutoFill Destination:=Range("S2:T" & MaxRowNum), Type:=xlFillDefault
    
[COLOR=#ff0000]After Autofill, there is no calculation done !![/COLOR]    
[COLOR=#0000CD]What kind of calculation?Usually I use this kind of code for Autofill and it works okay but except for this function :( 
[/COLOR]

    'Column S and T Autofit
    
    Columns("S:T").Select
    Columns("S:T").EntireColumn.AutoFit
    
    'Copy and Paste data as value
    'Columns("S:T").Select
    '=================================================
    ' Errors on Paste formulae/code
    '=================================================
    Range("S2:T" & MaxRowNum).Select
    Selection.Copy
    
[COLOR=#ff0000]Before Paste Activate the sheet. Or if i read it correctly you trying to paste data on the same same place where you are applying the formula
[/COLOR][COLOR=#0000CD]Yes , I'm trying to paste data on the same cell/place where I am applying the data. 
Because when report is given to the user , I dont want them to see the formula and only the values> [/COLOR]

    Worksheets("Simpat").Range("S2:T" & MaxRowNum).PasteSpecial xlPasteValues
    
    'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
    
    
    
    Application.CutCopyMode = False
    Range("S2").Select
'Close the speed up
With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
End With
'Close the error Handler
Exit Sub
errorFound:
If Err.Number > 0 Then MsgBox Err.Description, vbCritical, "Error#: & Err.Number"
Err.Clear
End Sub