Troubles with addition using macros

[Hojita]

Hello.

I'm currently working on a file as a way to learn excel and brush up on some skills. However I have gotten to a point where I need help. I have two sheets I am working with. Sheet1 has two buttons. One is "Win" and the other is "Loss". When selecting the "Win" button, I am wanting to have it send a + 1 to a column in Sheet2. The information that is displayed on Sheet1 in a specific cell is what I'm hoping to have show in the correct row of the selection. In case I haven't been able to explain what I am doing. This is the example.

Sheet1

D21:G21 is where the VLOOKUP is . The formula used is:<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Verdana; color: #000000}span.s1 {color: #008f00}</style>=VLOOKUP(Sheet2!A2:A349, RANDBETWEEN(2, 349))

The column in which the + 1, according to what is present in D21:G21, is G2:G349 in Sheet2.

I have been trying to achieve this using VBA, however my VBA skills are on a beginner level at this moment. The most I have been able to do is record a macro and then assign. But doing that with this is showing to be a bit more difficult. It is probably simple but I keep coming back to it and give up.

Thank you for the help.

 

[Scott T]

=VLOOKUP(Sheet2!A2:A349, RANDBETWEEN(2, 349))

I do not think the Lookup_value argument of vlookup can handle an array. You would have to put a single value to lookup. For the table_array you are returning a random number rather than a range to look in. I am not sure what you are trying to do.

 

[JLGWhiz]

I do not think the Lookup_value argument of vlookup can handle an array. You would have to put a single value to lookup. For the table_array you are returning a random number rather than a range to look in. I am not sure what you are trying to do.

I couldn't figure out what the return value was supposed to be either. I thought the VLookup formula was missing the Lookup_Value and just had the range array and a randomly generated column number. But that wouldn't work with only column A as the range array. I think the OP needs to do some work and clarify what the objective is.

 

[Hojita]

I may not had been able to say what I was trying to do. Essentially I'm trying to create a tally of win and losses but based off a random generator. I want to be able to select who won and who loss with a few button clicks.


So Sheet2 is my database page of "fighters". Column A has the "fighters names" that will display in D21:G21 on Sheet1. It randomizes the name each time and will select one from the database. When then a "fighter's name " displays in D21:G21, I want to be able to press a button and add 1 point (for a Win/ -1 point for a Loss) next to their name that is present in the column in Sheet2. The column that will hold this information is G2:G349 in Sheet2.


I tried building a fighting simulator based off stats to relearn how to do Excel.


Sheet1 - holds the name that is displayed of the "fighter" that is pulled from Sheet2's database of stats. This is where the selection for a "Win" and "Loss" buttons will be shown and used.
Sheet2 - holds the database of "fighters" and their stats, including a Win / Loss columns.


The Vlookup is used to bring in the "fighter's name" so it can be displayed in that merged cell.


I have included screen shots as well if it helps.

Above is Sheet2 database
Below is Sheet1

Hopefully this will help. Thank you once again.

 

[Scott T]

I think you want something like this. The randbetween will return a random row number to pull from giving you a random fighter.

=index(Sheet2!A2:A349, RANDBETWEEN(1, 349))

 

[Hojita]

Okay I will be trying that when I get the chance. Now when it comes to getting the win or loss buttons working, I as thinking of using macros to set that up. I figured that would be the best way when using the buttons. I've been struggling with getting the +1 (win) or -1 (loss)for the fighter to work when the buttons are clicked.