Help with a Spreadsheet Script

Bigpupp

Member
SoSH Member
Jun 8, 2008
1,743
New Mexico
Like you, I'm beyond bored and looking for anything to fit into the void that was once filled by baseball. So I decided to build a spreadsheet that would help me simulate the non-existent 2020 baseball season.

I think I've found a script that will help me with this process, but can't get it to work. The sheet I am working on is here if you want to give it a look, but the script I'm trying to run would allow me to push a button and have it use Vlookup to add 1 to a cell in the matching row. For example, if I click the plus button to the left of the Tigers, I would want it to add 1 to their season win total in column K.

Here's what I don't get...I can use the script below to successfully add to a specific cell:

function win1() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var yesCount = ss.getRange("k10");
var yesAdd = yesCount.getValue();
yesCount.setValue(yesAdd+1);
}

...but when I replace "k10" with a (working) Vlookup, I get an error that says "Exception: Range not found (line 3, file "Code")". The script I'm using ends up looking like this:

function win1() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var yesCount = ss.getRange("vlookup(S2,G1:L30,5,FALSE)");
var yesAdd = yesCount.getValue();
yesCount.setValue(yesAdd+1);
}

This is my first shot at writing a script, so any help would be appreciated.
 

LogansDad

Member
SoSH Member
Nov 15, 2006
18,066
Alamogordo
I am pretty new to scripting in vBA myself, so I won't be much help apart from maybe googling something for you.... but if this is up your alley, and you have any interest in PC games, you should consider looking into Out of the Park Baseball 21 (we have a thread in the video game forum).

I'm off to bed, like, now... but I will do some googling in the morning if nobody comes along to help, and see what I can figure out.
 

Bigpupp

Member
SoSH Member
Jun 8, 2008
1,743
New Mexico
So I'm actually wanting to use this spreadsheet with the game Deadball, which is basically a less robust (but cheaper!) version of the cards and dice version of Strat-O-Matic. I'm wanting to manually fill out some scorecards, and OOTP just doesn't fill that itch (but I will probably still put in a couple hundred hours into OOTP21 this year).
 
Last edited:

InstaFace

MDLzera
Silver Supporter
SoSH Member
Sep 27, 2016
12,520
Pittsburgh, PA
maybe getRange() is meant for a range, rather than a single value from which you can extract the value? I'd think you could probably use the getValue() on your reference to the spreadsheet directly. If not, then maybe it's interpreting your vlookup in the context of the range you've isolated and extracted and it needs to have references relative to the top-left of the range, not the sheet.

Either way, the right answer is CSFBL.
 

djbayko

Member
SoSH Member
Jul 18, 2005
12,196
Waltham, MA
Like you, I'm beyond bored and looking for anything to fit into the void that was once filled by baseball. So I decided to build a spreadsheet that would help me simulate the non-existent 2020 baseball season.

I think I've found a script that will help me with this process, but can't get it to work. The sheet I am working on is here if you want to give it a look, but the script I'm trying to run would allow me to push a button and have it use Vlookup to add 1 to a cell in the matching row. For example, if I click the plus button to the left of the Tigers, I would want it to add 1 to their season win total in column K.

Here's what I don't get...I can use the script below to successfully add to a specific cell:

function win1() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var yesCount = ss.getRange("k10");
var yesAdd = yesCount.getValue();
yesCount.setValue(yesAdd+1);
}

...but when I replace "k10" with a (working) Vlookup, I get an error that says "Exception: Range not found (line 3, file "Code")". The script I'm using ends up looking like this:

function win1() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var yesCount = ss.getRange("vlookup(S2,G1:L30,5,FALSE)");
var yesAdd = yesCount.getValue();
yesCount.setValue(yesAdd+1);
}

This is my first shot at writing a script, so any help would be appreciated.
Warning: I am a VBA novice.

Are you sure that you're able to use vlookup in the GetRange() function like that? I haven't looked too hard, but I can't find any examples of GetRange() being used with a formula.

I don't know why this would be necessary because you are working within the context of ActiveSpreadsheet, but another thing you could try is using explicit sheet/cell references in your vlookup (i.e. Sheet1!S2 rather than just S2).

ExcelForum and MrExcel are two forums where I've always had success getting answers to my most challenging problems.

Edit: Maybe this will help. VBA VLookup – A Complete Guide
 
Last edited:

Bigpupp

Member
SoSH Member
Jun 8, 2008
1,743
New Mexico
Warning: I am a VBA novice.

Are you sure that you're able to use vlookup in the GetRange() function like that? I haven't looked too hard, but I can't find any examples of GetRange() being used with a formula.

I don't know why this would be necessary because you are working within the context of ActiveSpreadsheet, but another thing you could try is using explicit sheet/cell references in your vlookup (i.e. Sheet1!S2 rather than just S2).

ExcelForum and MrExcel are two forums where I've always had success getting answers to my most challenging problems.

Edit: Maybe this will help. VBA VLookup – A Complete Guide
No. I'm not sure I can do it at all. I basically stole the +1 code from stack exchange and tried to re-purpose it using no knowledge of VBA.

Having said that, I have tried to more explicit in my references, and haven't had any luck with that. My project has basically changed from doing a baseball Sim, to just trying to learn script writing for Sheets at this point (which I'm fine with!) so I'll dive further into the rabbit hole and check out that link.
 

djbayko

Member
SoSH Member
Jul 18, 2005
12,196
Waltham, MA
No. I'm not sure I can do it at all. I basically stole the +1 code from stack exchange and tried to re-purpose it using no knowledge of VBA.

Having said that, I have tried to more explicit in my references, and haven't had any luck with that. My project has basically changed from doing a baseball Sim, to just trying to learn script writing for Sheets at this point (which I'm fine with!) so I'll dive further into the rabbit hole and check out that link.
Yeah, I think that link is the way to go. Basically, use VBA methods to emulate what you wanted to do using the formula.