How to Stop Macro if Cells Are Blank

[Auebon Crosby]

Essentially, I'm attempting to create a 'static timestamp.' To my understanding, the only way to do it is to create a dynamic one, copy the value, and then paste it with 'Values Only.' To make this easy, I'm attempting to create a macro for a button to do this. The problem is, the macro I have now is erasing the empty cells' with formulas in the them. I want the macro to kill its function if there's nothing in the blank cell besides a formula waiting to take effect (if that makes sense). 

Here's the function of the formula:

Cell A1 > if blank, B1 is blank

Cell A1 > if any value is in the cell, B1 creates a dynamic timestamp

So in the entirety of column B, there's a formula: =IF(ISBLANK(A1),"",NOW()) - and subsequently changes, A1 > A2 > A3, etc. to the corresponding B cell.

Here is the macro that I'm currently using in Apps Script: 

function myButton2() {

  var spreadsheet = SpreadsheetApp.getActive();

  spreadsheet.getRange('B3:B').activate();

  var currentCell = spreadsheet.getCurrentCell();

  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();

  currentCell.activateAsCurrentCell();

  spreadsheet.getRange('B3:B').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

};

As of now, there are timestamps up to B95 because there are values up to A95. Again, if I run the above macro, it 'Values Only' pastes all of the timestamps through to B95; however, it then subsequently removes the formulas between B96:B because it copied nothing and pasted the value of nothing.

What do I need to add to my macro so that it kills itself at B96 (or the first respective blank B value) because there's no timestamp in it due to cell A96 being blank? (If that makes sense.) I appreciate any and all guidance.

[Laurie Nason]

Hi Auebon,

Why don't you just use the onEdit(e) event to put in a value into your date/time column when the appropriate column is edited? If you have multiple tabs in your sheet you'll need to check that the thisSheet.getName() is equal to the value of the tab your looking for. No formulas required in the sheet itself.

function onEdit(e) {

//Column to check if we're modifying

var colToCheck=1;

//Column to put the date into

var colDate=2;

var thisSheet=e.source.getActiveSheet();

var modRange=e.range;

if(modRange.getColumn()==colToCheck){

if(modRange.getValue()!==''){

//now check if there's a date there already

if(thisSheet.getRange(modRange.getRow(),colDate).getValue()==''){

thisSheet.getRange(modRange.getRow(),colDate).setValue(new Date());

}

}

}

return;

}

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/2b03af9b-efa8-4356-9189-a36bd2a36492n%40googlegroups.com.

--

Laurie