How to use index/match to insert the date a button was clicked using VBA

[Javelin]

Hi all,

I'm trying to add a function to a button. Currently the button creates and prints an invoice when clicked. I have an invoice tracker which tracks the invoices nearly automatically. But i need to manually enter the date the invoice was created. I'd like to add the function that when i click the button, it does an index/match search for the invoice number (located in a nearby cell) in the invoice tracker and upon finding inserts todays date into the appropriate cell next to it. Here is what I have so far:

test = Application.WorksheetFunction.Index(Sheets("Sheet1").Range("M3:N13"), Application.WorksheetFunction.Match(Range("c4"), Sheets("Sheet1").Range("M3:M13"), 0), 2).Value = Date

I have tried several variations of this but cannot seem to get it to work. I doesn't trigger any errors or warnings. It just doesn't do anything. and I've exhausted my google abilities trying to find a solution. Can any one help me understand why its not working and how to make it work? Thanks in advance.

-J

 

[EFANYoutube]

Hi Javelin

Let me know if this works

Sub WriteDate()
    Sheet1.Range("M:M").Find(Sheet2.Range("C4")).Offset(, 1).Value = Date
    'sheet1 range M:M is the vba name & range of the sheet you want to find the invoice number in
    'sheet2 range C4 is the vba name & range of the sheet where the invoice number your wanting to write the date to the right of is
    
End Sub

 

[Javelin]

Thanks. I entered it and i can't figure out why but I get error 91 "Object variable or with block variable not set" when i click the button . I dont have any variables... so I'm not sure how to correct it.

 

[Javelin]

Never mind! I realized I had hidden column A and forgot so I was entering column A when in reality it was B. It works. Thanks!!