New to VBA, need to do Vlookup after a value is entered

[Buzz1126]

I have a couple VBA questions as this is my first time taking on a project involving VBA.

What I want to happen:
1. The user presses the button and an inputBox appears asking to input an auction number and an auction seller. These values are stored under variables AuctionNum and AuctionSeller.
2. Today's date is updated to the current date once the button is pressed and is stored in the variable TodayDate. This value is placed in the format mm/dd/yy in cell H2.
3. Vlookup formulas search through the database tab in cells B3:F30 to return the auction date in cell c7 and the total cars in cell c9. These values are stored in the variable AuctionDate and TotalCars.



The code I have been working on is currently under module 1 and i apologize if the formatting or anything is wrong. This is my first crack at something like this and am trying to learn.

I hope this makes sense and will try to answer any questions promptly.

Thank you,

Matt

**I'm using excel 2016 for Mac

[Buzz1126]

Still having trouble with this. I'm getting an error box after i run the code that says 400.

Please Login or Register  to view this content.

I just need to use the inputed value in the input box for a Vlookup function. That's all I'm trying to do.

[protonLeah]

1. VLookup finds a match in a column and returns a value from a column to the right not the left.

2. In order to use VLookup, the auction numbers must be in the first column of the database, not the second. As it is, you are looking for the auction number string in a column of dates (excel numbers).

3. Your textbox is returning a text string, say "50", while the auction numbers in the database are Excel number values. So, even if the first column were auction numbers, VLookup would fail because "50" is not 50. So, in addition to being the first column, the auction number column must be formatted as text not general. Alternatively, you can convert the textbox value from string to integer before the lookup.

4. To calculate the total cars for an auction# you will have to use Countif()

[AlphaFrog]

What Ben said. Try something like this...

Note: format cell C7 with any date format you like.

Please Login or Register  to view this content.

[Buzz1126]

Worked perfectly, thank you both so much.