VBA to Office Script Help

Hello.   I have a VBA function that I would like converted to script in order for it to work on the sharepoint version.  Please see below.  If "Yes" is entered via drop down in column N, the line is copies to the historical referrals then deleted.  Thank you!

Private Sub Worksheet_Change(ByVal Target As Range)
'   Check to see only one cell updated
    If Target.CountLarge > 1 Then Exit Sub
    
'   Check to see if entry is made in column B after row 5 and is set to "Yes"
    If Target.Column = 14 And Target.Row > 1 And Target.Value = "Yes" Then
        Application.EnableEvents = False
'       Copy columns B to I to complete sheet in next available row
        Range(Cells(Target.Row, "A"), Cells(Target.Row, "N")).Copy Sheets("Historical Referrals").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
'       Delete current row after copied
        Rows(Target.Row).Delete
        Application.EnableEvents = True
    End If
'ActiveSheet.Protect
'Target.Calculate

End Sub

ItsNursinTime 

To convert your VBA code to an Office Script that can be used in Excel Online or in the Teams version of Excel, we'll need to rewrite the code using the syntax and features supported by Office Scripts. Unfortunately, not all VBA functionality is available in Office Scripts, so we may need to make some adjustments to your code.

Below is a version of your VBA code converted to an Office Script. This script will be triggered whenever a change is made in the worksheet, similar to the Worksheet_Change event in VBA:

Javascript code is untested, please backup your file.

function main(workbook: ExcelScript.Workbook, sheet: ExcelScript.Worksheet, changedRange: ExcelScript.Range) {
  const KEYWORD: string = "Discontinued";
  const UID: string = "Unique ID";
  const DEST_SHT: string = "Archived_CVL";

  let targetCell = changedRange.getResizedRange(0, -8);
  let targetValue = targetCell.getValue();

  if (targetValue && typeof targetValue === 'string' && targetCell.getColumn() === 9 && targetCell.getRow() > 3 && targetValue.toLowerCase() === KEYWORD.toLowerCase()) {
    let listObject = sheet.getListObjects()[0];
    let sID = sheet.getRange(targetCell.getRow(), 1).getValue();

    if (listObject) {
      listObject.getAutoFilter().clear();
      listObject.getAutoFilter().applyFilter(1, sID);

      let visibleRange = listObject.getRange().getSpecialCells(ExcelScript.SpecialCellType.visible);
      if (visibleRange) {
        let destSheet = workbook.getWorksheet(DEST_SHT);
        let destRange = destSheet.getRange(destSheet.getUsedRange().getRowCount() + 1, 1);
        visibleRange.copyTo(destRange, ExcelScript.CopyToType.all, false);
        visibleRange.delete(ExcelScript.DeleteShiftDirection.up);
      }
    }
  }
}

Here are some key differences and considerations:

1. We use TypeScript syntax for Office Scripts, which is similar to JavaScript.
2. We use the main function as the entry point for the script.
3. We use the getResizedRange method to adjust the target range by moving 8 columns to the left.
4. We use methods like getValue, getRange, getListObjects, getAutoFilter, and getSpecialCells to manipulate the worksheet data and objects.
5. We use toLowerCase() to perform case-insensitive string comparison.

Please note that Office Scripts are still evolving, and not all VBA functionality is available. You may need to adapt your code further or explore alternative solutions depending on your requirements and the features available in Office Scripts. The text, steps and the script was created with the help of AI.

Formularbeginn

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.