Troubles with addition using macros

Hojita

New Member
Joined
Feb 18, 2017
Messages
7
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.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,622
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
=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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Feb 18, 2017
Messages
7
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.




Acp3bmL.png

Above is Sheet2 database
Below is Sheet1


25tMuz5.png

Hopefully this will help. Thank you once again.
 

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,622
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I think you want something like this. The randbetween will return a random row number to pull from giving you a random fighter.
Code:
=index(Sheet2!A2:A349, RANDBETWEEN(1, 349))
 
Last edited:

Hojita

New Member
Joined
Feb 18, 2017
Messages
7
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,685
Messages
5,654,747
Members
418,149
Latest member
amamiche67

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