none
Replacing a cell with a formula RRS feed

  • Question

  • I have a cell B11 that shows info from another page. If someone types in information into this cell, we all know that the formula gets deleted. But in this case, this needs to happen, so after the VBA script has run and transferred the new values to a second sheet  I need the original script to be entered back into the field. 

    I need this to happen two ways. 

    One just the generic replace formula script. 

    So in English I need a script that replaces only B11 with this code  =IFERROR(VLOOKUP($AG$3,'NCMR Data'!$A$2:$Y$999999,2,FALSE),"") 

    B11 is part of a merged cell, so a heads up there.

    The second thing I need is to make this script run on closing of sheet. 

    Thanks for the help in advance.

    Friday, May 4, 2012 5:41 PM

Answers

  • If you look at my previously reply, you'll see that I had

        Range("B11").Formula = _
            "=IFERROR(VLOOKUP($AG$3,'NCMR Data'!$A$2:$Y$999999,2,FALSE),"""")"
    

    (plus a worksheet reference, but that is not relevant). Please note the four consecutive double quotes. They were not a mistake, but intentional. If you want to include a double quote " inside a quoted string, you must double it to "". If you want to include two double quotes "" to specify an empty string, you must double them to """".

    Regards, Hans Vogelaar

    • Marked as answer by Matt Ridge Friday, May 4, 2012 7:56 PM
    Friday, May 4, 2012 7:38 PM

All replies

  • The second thing I need is to make this script run on closing of sheet. 

    Do you mean when the workbook is closed? Or when the user activates another worksheet within the workbook? And if the latter, what if the user doesn't activate another worksheet but simply closes the workbook?

    Regards, Hans Vogelaar

    Friday, May 4, 2012 6:48 PM
  • When someone physically closes the entire workbook, the reason is so that just in case someone forgets to press update, or does something maliciously to the page people can keep working. I will be locking the page down but this is a just in case scenario. 

    That script is going to be in my "arsenal" just in case someone demands it, because I know someone will, I just am doing a CYA in my case. 


    Friday, May 4, 2012 7:06 PM
  • Press Alt+F11 to activate the Visual Basic Editor.

    Double-click the ThisWorkbook node in the Project Explorer on the left hand side.

    Copy the following code into the module that appears:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
        Worksheets("MySheet").Range("B11").Formula = _
            "=IFERROR(VLOOKUP($AG$3,'NCMR Data'!$A$2:$Y$999999,2,FALSE),"""")"
    End Sub

    Replace MySheet with the name of the relevant sheet. I used the Before Save event of the workbook - it's only necessary to restore the formula when the workbook is saved, not when the workbook is closed (possibly without saving it).


    Regards, Hans Vogelaar

    Friday, May 4, 2012 7:22 PM
  • What you wrote will work, but not exactly the way I have in mind...

    This is part of a much larger script, as I said before, what you have will work if it was by itself.

    This is the original script.

    Sub PENCMR()
        Dim i As Integer
    
        'Internal NCMR
        Dim wsPE As Worksheet
        Dim wsNDA As Worksheet
        Dim c As Variant 'Copy Ranges
        Dim P As Range 'Paste Ranges
    
        Application.ScreenUpdating = False
    
        'Setting Sheet
        Set wsPE = Sheets("Print-Edit NCMR")
        Set P = wsPE.Range("A54:U54")
    
        Set wsNDA = Sheets("NCMR Data")
    
        c = Array("AG3", "B11", "B14", "B17", "B20", "B23" _
                , "Q11", "Q14", "Q17", "Q20", "R25", "V23" _
                , "V25", "V27", "B32", "B36", "B40", "B44" _
                , "D49", "L49", "V49")
    
        For i = LBound(c) To UBound(c)
            P(i + 1).Value = wsPE.Range(c(i)).Value
        Next
    
        With wsNDA
            Dim NR As Long, LR As Long, LC As Long
            Dim f As Range
    
            LR = .Range("C" & Rows.Count).End(xlUp).Row
            LC = .Cells(2, Columns.Count).End(xlToLeft).Column
            NR = LR + 1
    
            'find matching row if it exists
            Set f = .Range("A3:A" & LR).Find(what:=P.Cells(1).Text, LookIn:=xlValues, lookat:=xlWhole)
            If Not f Is Nothing Then
                f.Resize(1, P.Cells.Count).Value = P.Value
            Else
                MsgBox "The data can't be shown, please review the data in question, if no problem can be found please contact the developer"
            End If
        End With
        Range("A54:U54").ClearContents
        Range("B11").Formula = "=IFERROR(VLOOKUP($AG$3,'NCMR Data'!$A$2:$Y$999999,2,FALSE),"")"
        Application.ScreenUpdating = True
        
        
    End Sub
    
    
    
    

    You can see where I am trying to enter it into. I have tried what you attempted to an extent and so far it comes back telling me this:

    Run-time error '1004':
    Application-defined or object defined error.

    At:

        Range("B11").Formula = "=IFERROR(VLOOKUP($AG$3,'NCMR Data'!$A$2:$Y$999999,2,FALSE),"")"



    • Edited by Matt Ridge Friday, May 4, 2012 7:30 PM
    Friday, May 4, 2012 7:28 PM
  • If you look at my previously reply, you'll see that I had

        Range("B11").Formula = _
            "=IFERROR(VLOOKUP($AG$3,'NCMR Data'!$A$2:$Y$999999,2,FALSE),"""")"
    

    (plus a worksheet reference, but that is not relevant). Please note the four consecutive double quotes. They were not a mistake, but intentional. If you want to include a double quote " inside a quoted string, you must double it to "". If you want to include two double quotes "" to specify an empty string, you must double them to """".

    Regards, Hans Vogelaar

    • Marked as answer by Matt Ridge Friday, May 4, 2012 7:56 PM
    Friday, May 4, 2012 7:38 PM
  • Ahh, I didn't understand why the extra " were for. 
    Friday, May 4, 2012 7:41 PM