How to VBA update a cell value based on another cell

Question

I've looked all over stack exchange and have not seen exactly what I'm looking for. I have an extensive list of some items that need constant calibration at different dates and intervals, and what I'm trying to do is make a VBA/Macro so that I can update any one specific item's calibration date with out having to look at each individual entry or using =VLOOKUP(). so far I have nothing solid I can call a program yet, but what I had envisioned was in a cell write the item's ID#, in another cell the new calibration exp date and have a button next to them so that is searches for the ID#, moves one column to the right and replace the text on the cell with the new date. Simple enough to explain but given the fact that I just started learning about VBA four hours ago, it's proving to be a challenge. So far I've toyed with some code to no avail:

range("a1").Formula = "=CELL("address", INDEX(H:J, MATCH(L11,H:H, 0),2))"

ActiveCell.FormulaR1C1 = "=INDEX(R[-5]C[-9], 3, 2, )"

I will provide more information or context if anyone needs it!

Answer 1

When using quotes inside a formula you should double up on them:

Range("A1").Formula = "=CELL(""address"", INDEX(H:J, MATCH(L11,H:H, 0),2))"
ActiveCell.FormulaR1C1 = "=INDEX(R[-5]C[-9], 3, 2, )"

Answer 2

This will get you pretty close. You can adjust this to fit your data. The commented out MsgBox commands can be deleted, but are handy for debugging.

Sub SetCalDate()

    'Set up variables
    Dim ID, RowNum As Integer
    Dim NewCalDate As Date
    Dim IDNums As Range

    'Initialize variables
    ID = Range("A1").Value           'Assumes the ID to Find is in A1,
    NewCalDate = Range("B1").Value   'and the NewCalDate is in B1.
    'MsgBox NewCalDate
    Set IDNums = Range("C1:C10")     'This is the range of ID's to be searched.
    'You might need something like Set IDNums = Worksheets("Sheet3").Range("C1:C10")
    'if the ID numbers are on a different sheet.

    'Find the ID in IDNums
    RowNum = Application.WorksheetFunction.Match(ID, IDNums, 0)
    'MsgBox RowNum
    Range("D" & RowNum) = NewCalDate    'Assumes the CalDates are in column D.

End Sub

If you want to assign the macro to a button, put the code in a new module first, then make a button and assign the SetCalDate() macro to it as explained on this help page. Good luck.