Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>If you must use VBA, the simplest way would be to just re-write the formula in the affected cells:</p> <p>First, place this in the Worksheet's module. This will cause the macro to fire EVERY time a change is made to the column A.</p> <pre><code>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 </code></pre> <p>Then, place this in an ordinary code module:</p> <pre><code>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 </code></pre> <p>So, programmatically inserting a normal formula is fairly easy.</p> <p>The question then becomes how often do you want to force/overwrite these cells? You can tie this macro to "events" like, whenever the workbook file is opened, or whenever a value on the sheet changes, or whenever someone manually changes the cells you don't want them to change, etc.</p> <p>Your second formula you could do the same thing with it, just add another Range variable (e.g., <code>Dim rng2 as Range</code>) and another string variable to hold the formula text (e.g., <code>strFormula2</code>). </p> <p>Alternatively, you could "rewrite the formula" purely in vba. Replace <code>cl.Formula = strFormula</code> with <code>cl.Value = MyLookupFormula</code> and add this function to the code module containing the subroutine above:</p> <pre><code>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 </code></pre> <p>But that requires knowing more about how often/what events will trigger this macro, since the cells will not have any formula (the formula/computation is performed in memory ONLY when requested by user or an event trigger).</p>
    singulars
    1. This table or related slice is empty.
    1. POHow to convert cell vlookup to VBA
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USDavid Zemens
    1. USDavid Zemens
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks David. But how do I make it when Column A have value or is not blank, then it will run the following code?
      singulars
      1. PO
      1. USuser1935007
    2. COThe condition you describe can be further built in to the code above with a simple `If` statement -- for example you could change `cl.Formula = strFormula` to `If cl.Value <> "" Then cl.Formula = strFormula`, or from the second example, `If cl.Value <> "" Then cl.Value = MyLookupFormula`, but that is not sufficient instruction to CAUSE or initiate execution of the code. Code can be executed manually, or tied to "events". The code I provide has to be executed manually because you have not indicated it should be an event-driven macro.
      singulars
      1. PO
      1. USDavid Zemens
    3. COHow to I make a event if Column A have value or is not blank, then it will run the following code for Column B. The formulae is for column B, but the primary key will be Column A to trigger it. Hence once there is value in Column A, it should run the code for Column B
      singulars
      1. PO
      1. USuser1935007
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload