How to convert cell vlookup to VBA
I currently have a formula in my cell:
=IFERROR(VLOOKUP(A:A,'Daily Report'!A:Z,2,FALSE),"")
=IFERROR(VLOOKUP(A:A,'Daily Report'!A:Y,7,FALSE)&", "&VLOOKUP(A:A,'Daily Report'!A:Y,8,FALSE)&", #"&VLOOKUP(A:A,'Daily Report'!A:Y,9,FALSE)&"-"&VLOOKUP(A:A,'Daily Report'!A:Y,10,FALSE)&", Singapore "&VLOOKUP(A:A,'Daily Report'!A:Y,11,FALSE),"")
How do I convert it to VBA so that the entire column is encrypted with this formula?
My formula is always replaced by guys using my excel sheet.
I am avoiding cell locking, so am looking at VBA to do this.
Edited:
MACRO
Sub vlookup()
Dim LR As Long
LR = Cells(Rows.Count, "A").End(xlUp).Row
Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(C[-3],'Daily Report'!C[-3]:C[22],2,FALSE),"""")"
Selection.AutoFill Destination:=Range("D2:D" & LR), Type:=xlFillDefault
End Sub
Now how to make data, eg. 09-02-18022013-03383-A, when you enter column A, it will run a macro to enter legal data.
source to share
If you must use VBA, the simplest way would be to just rewrite the formula in the affected cells:
First put this in the Worksheet module. This will cause the macro to run EVERY time a change occurs in column A.
Private Sub Worksheet_Change(ByVal Target as Range)
If Not Intersect(Target,Me.Range("A:A")) Is Nothing Then
Application.EnableEvents = False 'to disable infinite loop
InsertFormula
Application.EnableEvents = True
End If
End Sub
Then put this in a normal code module:
Sub InsertFormula()
Dim rng as Range 'this will set the range in which you want this formula to appear
Dim cl as Range 'cell iterator within rng variable
Dim strFormula1 as String `string to hold the formula text
set rng = Range("B2:B39") 'Change this range to the desired range
strFormula = "=IfError(Vlookup(A:A,'Daily Report'!A:Z,2,False),"")"
For Each cl in rng
cl.Formula = strFormula
Next
End Sub
So, programmatically inserting a normal formula is pretty easy.
The question then becomes, how often do you want to force / overwrite these cells? You can bind this macro to "events", for example, whenever a workbook file is opened, or when a value in a worksheet changes, or when someone manually changes cells that you don't want to change, etc.
Your second formula that you could do the same with, just add another Range variable (for example Dim rng2 as Range
) and another string variable to hold the formula text (for example strFormula2
).
Alternatively, you can "rewrite the formula" exclusively in vba. Replace cl.Formula = strFormula
with cl.Value = MyLookupFormula
and add this function to the code module containing the subroutine above:
Function MyLookupFormula() as Variant
'Performs equivlanet to worksheet function
If Not IsError(Application.WorksheetFunction.Vlookup(Range("A:A"),Sheets("Daily Report").Range("A:Z"),2,False)) Then
myLookupFormula = (Application.WorksheetFunction.Vlookup(Range("A:A"),Sheets("Daily Report").Range("A:Z"),2,False))
Else: myLookupFormula = vbNullString
End Function
But this requires knowing more about how often / what events this macro will fire, since the cells will not have any formula (the formula / calculation is done in memory ONLY by user request or event trigger).
source to share