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
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

EFANYoutube

Board Regular
Joined
May 19, 2017
Messages
162
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!!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,588
Messages
5,597,049
Members
414,117
Latest member
marblepoint

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