0

I want to loop through a range of cells of a column and increment only the non-empty cells by 1, with a click of a single button.

I tried a code that was later found to be for VBA Excel and not for Google Sheets; which I'm currently looking for.

The blank cells should remain unaffected. Thank you.

M4k
  • 11
  • 3
  • 2
    This is VBA/Excel. Is there a reason you have the question tagged with google-apps-script and google-sheets? – BigBen Jun 18 '19 at 16:37
  • 1
    what does `but to no avail` mean? What is it doing that it shouldn't or not doing what it should? What errors if any are popping up? – Scott Craner Jun 18 '19 at 16:38
  • Hi, I added those tags as the issue was quite similar to the one mentioned here: https://stackoverflow.com/q/32813855/10370106 – M4k Jun 18 '19 at 16:45
  • I get the error "Missing ; before statement. (line 1, file "Code")" – M4k Jun 18 '19 at 16:48
  • Can you please clarify - are you working in Excel, or Google Sheets? – BigBen Jun 18 '19 at 16:52
  • I'm working on Google Sheets – M4k Jun 18 '19 at 16:55
  • 1
    You've posted VBA code. VBA does not work in Google Sheets. VBA and Google Apps Script are completely different. – BigBen Jun 18 '19 at 16:56
  • Can you please [edit your question](https://stackoverflow.com/posts/56653494/edit) to remove the extraneous tag, clarify what platform you are using and show us the code your are actually using in Google Sheets? – cybernetic.nomad Jun 18 '19 at 17:25

1 Answers1

1

Increment Non Empty Cells with a single Button

function incrementNonEmptyCellsInARange(rA1) {
  var rA1=rA1 || 'A1:C3';
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Sheet1');
  var change=false;
  try {
    var rg=sh.getRange(rA1);
  }
  catch(e) {
    SpreadsheetApp.getUi().alert(e + '\r\n Current Range: ' + rA1);
    return;
  }
  if(rg) {
    var vA=rg.getValues();
    for(var i=0;i<vA.length;i++) {
      for(var j=0;j<vA[i].length;j++) {
        if(typeof vA[i][j] == 'number') {
          vA[i][j]+=1;
          change=true;
        }
      }
    }
    if(change) {
      rg.setValues(vA);
    }
  }
}

Here's the button

And a textbox to enter the desire range in A1 Notation.

function showMySideBar() {
  var html='Range: <input type="text" id="txt1" /><br /><input type="button" value="Increment" onClick="doIt();" />';
  html+='<script>function doIt(){var txt=document.getElementById("txt1").value;google.script.run.incrementNonEmptyCellsInARange(txt);}console.log("My Code");</script>';
  var userInterface=HtmlService.createHtmlOutput(html);
  SpreadsheetApp.getUi().showSidebar(userInterface);
}
Cooper
  • 59,616
  • 6
  • 23
  • 54