none
i have cretaed one vlooup formula in acell how can i hide the formuala and can i enter any data in that cell without overwriting the formula RRS feed

  • Question

  • I have one cell where I pick data through vlookup and if the condition is wrong can I enter manual data in that cell, now if if no data found in vlookup its showing n/a, and can we hide the formula of that cell.
    Monday, March 27, 2017 4:25 AM

Answers

  • The VBA code below will allow the formula in the cell and if overwritten with a value then the formula is overwritten. However, if the value is deleted from the cell then the VBA code re-inserts the formula.

    Both the code and formula is for one specific cell only. If you want a range of cells to work this way then you will need to provide me with the range of cells and the code will be significantly different.

    To install the VBA code:

    1. Right click the worksheet tab and select View code to open the VBA editor at the worksheets code module.
    2. Copy the VBA code below and paste into the VBA editor. (Do not change the Sub name)
    3. Edit the cell address where I have the comment that appears in green in the VBA editor.
    4. Close the VBA editor (Cross very top right of VBA editor window)
    5. Save the workbook as Macro enabled.
    6. Ensure macros are enabled. See help for how to do this. (Option to "Disable all macros with notification" should be OK.)

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim rng As Range
       
        On Error GoTo ReEnableEvents
       
        'Following line. Edit "$F$9" to cell address with formula or entered value
        If Target.Cells.Count = 1 And Target.Address = "$F$9" Then
            Application.EnableEvents = False
            If Target.Formula = "" Then
                Target.Formula = "=IFERROR(VLOOKUP(D9,Sheet2!A2:C97626,3,FALSE),"""")"
            End If
        End If

    ReEnableEvents:
        If Err.Number <> 0 Then
            MsgBox "An error occurred in Private Sub Worksheet_Change"
        End If
        Application.EnableEvents = True
    End Sub


    Regards, OssieMac

    • Edited by OssieMac Monday, March 27, 2017 6:56 AM
    • Proposed as answer by Chenchen LiModerator Tuesday, March 28, 2017 1:51 AM
    • Marked as answer by DEEPU1980 Tuesday, April 4, 2017 8:51 AM
    Monday, March 27, 2017 6:56 AM

All replies

  • The following example will display the cell as blank if lookup value not found.

    =IFERROR(VLOOKUP(D2,$A$1:$B$8,2,FALSE),"")

    You can manually enter a value where the formula exists. However, the formula will be overwritten.


    Regards, OssieMac

    Monday, March 27, 2017 4:45 AM
  • so without overwriting the formula how can we enter manual data if the condition is wrong ,,

    my formual in that cell is   =VLOOKUP(D9,Sheet2!A2:C97626,3,FALSE)  please help with the formula or vb code

    Monday, March 27, 2017 5:22 AM
  • The VBA code below will allow the formula in the cell and if overwritten with a value then the formula is overwritten. However, if the value is deleted from the cell then the VBA code re-inserts the formula.

    Both the code and formula is for one specific cell only. If you want a range of cells to work this way then you will need to provide me with the range of cells and the code will be significantly different.

    To install the VBA code:

    1. Right click the worksheet tab and select View code to open the VBA editor at the worksheets code module.
    2. Copy the VBA code below and paste into the VBA editor. (Do not change the Sub name)
    3. Edit the cell address where I have the comment that appears in green in the VBA editor.
    4. Close the VBA editor (Cross very top right of VBA editor window)
    5. Save the workbook as Macro enabled.
    6. Ensure macros are enabled. See help for how to do this. (Option to "Disable all macros with notification" should be OK.)

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim rng As Range
       
        On Error GoTo ReEnableEvents
       
        'Following line. Edit "$F$9" to cell address with formula or entered value
        If Target.Cells.Count = 1 And Target.Address = "$F$9" Then
            Application.EnableEvents = False
            If Target.Formula = "" Then
                Target.Formula = "=IFERROR(VLOOKUP(D9,Sheet2!A2:C97626,3,FALSE),"""")"
            End If
        End If

    ReEnableEvents:
        If Err.Number <> 0 Then
            MsgBox "An error occurred in Private Sub Worksheet_Change"
        End If
        Application.EnableEvents = True
    End Sub


    Regards, OssieMac

    • Edited by OssieMac Monday, March 27, 2017 6:56 AM
    • Proposed as answer by Chenchen LiModerator Tuesday, March 28, 2017 1:51 AM
    • Marked as answer by DEEPU1980 Tuesday, April 4, 2017 8:51 AM
    Monday, March 27, 2017 6:56 AM