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

Javelin

New Member
Joined
Jan 8, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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:

VBA Code:
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

EFANYoutube

Active Member
Joined
May 19, 2017
Messages
380
Office Version
  1. 365
Platform
  1. Windows
Hi Javelin

Let me know if this works
VBA Code:
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
 
Solution

Javelin

New Member
Joined
Jan 8, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Jan 8, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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!!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,668
Messages
5,838,680
Members
430,563
Latest member
Raeyven

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top