Lock Cell after vlookup

A

aneela1

New member
Joined
Nov 21, 2015
Messages
47
Reaction score
0
Points
0
Hi,
When I enter data into Column A, Vlookup gets the corresponding value from the source and enters into Column B.
However I also sometimes need to change the data in the source, say for Jane if I have corresponding value 3, I may need to change it to 4 and then it would affect all the values in Column A.
Is there a way to prevent this and lock the cell values where the Vlookup has worked.

Regards,
Aneela
 
Only through vba code/macro. A file would be useful.
 
Please find attached. I have highlighted the cells that are lookup fields and that of results.

Regards,
Aneela
 

Attachments

  • Tech.xlsx
    14.4 KB · Views: 195
Hey aneela! Interesting question here.

If you think about it, "locking" a cell is actually quite simple -- all you need to do is replace a formula with its result and boom! The cell is locked :)

You could attach the following heavily-commented code to a button that should do the trick, but beware! ... This subroutine is "destructive" -- it replaces formulas with values in column D. (As another note, it appears that you have duplicates in your lookup table, which will definitely cause you problems in the future.)

Code: 
Option Explicit
Public Sub ConvertLookupsToValues()

    Dim lngLastRow As Long, lngIdx As Long
    Dim varLookups As Variant
    
    'Everything happens on the Ledger sheet
    With ThisWorkbook.Worksheets("Ledger")
    
        'Identify the last-occupied row in column D on the Ledger sheet,
        'then store all the values in a column array (variant type)
        lngLastRow = .Cells(.Rows.Count, 4).End(xlUp).Row
        varLookups = .Range(.Cells(3, 4), .Cells(lngLastRow, 4))
        
        'Loop through the array, writing values over the existing
        'formulas if the lookup was successful
        For lngIdx = 1 To UBound(varLookups)
            If varLookups(lngIdx, 1) <> 0 Then
                .Cells(2 + lngIdx, 4) = varLookups(lngIdx, 1)
            End If
        Next lngIdx
        
    End With

End Sub
 
Hi,
Thanks. I was wondering perhaps this could be done per row when it is filled it and at the time it is filled it.
It goes like this:
1. From the sheets software that you have seen, the user clicks and the data is entered into ledgers sheet.
2. The row is filled and the Vlookup finds and puts the result.
3.How about at that instant the data is freezed and the rest of the cells continue to have the forumula.

Thanks
regards,
Aneela
 
Why not get the code to put the right value at the same time the row is being added?
Then no formulas at all in that column.
In your Button_3_Click macro, in the ledger section, after the lines:

ws1.Range("B8").Copy
Workbooks(wb).Sheets("Ledger").Range("C" & ws4lngTargetRow).PasteSpecial~~~~

add something like:
zz = Application.VLookup(Workbooks(wb).Sheets("Ledger").Range("C" & ws4lngTargetRow).Value, Workbooks(wb).Sheets("Rates").Range("A3:B14"), 2, False)
Workbooks(wb).Sheets("Ledger").Range("D" & ws4lngTargetRow) = IIf(IsError(zz), 0, zz)
 
Last edited:
Hi,
p45cal, that would not be ok, since taking the rates from within the ledger workbook will allow the user to change the values as per the customer. I want it to be changed at the instance the item is added to the ledger row. Danwagnerco's formula works, but it clears the entire sheet of the formulas and does not lookup the values for the new entries added to ledger, so it does not work. Please help me with this.

regards,
Aneela
 
You've lost me.
I think it does the equivalent; your sheet's vlookup formula gets the info from the Rates sheet, so does my code. The value is hard coded, not a formula, hence 'locked' in that sense. My code will retain old values of the vlookup and will use the current Rates table at the instant a new line is added.
I give up.
 
I am sure I could not have explained this. Please see the attachment in Post#3. I am concerned with this workbook only. I mean when the data is copied to the Column C and the VLOOKUP has done it work in Column D, then the formula finishes and the value becomes permanent. I want to use the Sheet("Rates") within this workbook.
Regards,
Aneela
 
That's what code in msg#6 does.
 
OMG I am sorry. You are right it does work, sorry for troubling you. How do I get rid of the message that displays everytime I open excel for Macro enabling. I want macros enabled at all times.

Regards,
Aneela
 
You must log in or register to reply here.
Back
Top