Using Variables to represent Row numbers in an Excel Office script

[BrainStain]

Im wanting to make it easy for users in my group to Archive sections of a sheet.

Each user has their own sheet in a workbook of 10 users.

Each sheet can have up to 100 jobs on their sheet.

Each job consists of 15 rows with various related data to just that job.

When that job is completed, it needs to be Archived to another sheet in the same workbook.

Every week new jobs will get added to each users sheet at the top, meaning all the existing jobs on that sheet get bumped down 15 rows per job added.

I want to make it easy for the user to Archive a job when its finished, by finding that job on their sheet, and enter the excel "row number" where it starts (not cell number like B10, but row 120) where the job starts, and also the excel row number where it ends, and enter those 2 values on their sheet in cells A1 and A2. Not interested in doing a Search by job number right now, maybe later, just the Top and Bottom rows of that job of 15 rows that the user will find.

My script will pull those values from those cells, which will create a Range for the script to copy and paste that one job to the Archive sheet. I will use those same rows to delete the job from the users sheet after copied.

I have 90% of the script written, but dont know how to convert the values the user entered at A1 and A2 into a Range so the script can go down that users sheet to copy and paste to the Archive sheet.

Here is what I have so far, and please remember, it works Perfectly if I actually enter the row numbers on the copyFrom line, but those row numbers increase as new jobs get Inserted weekly at the top, which is why Im using variables to pull in the users entry at A1 and A2 which will represent where on the sheet that specific job to Archive is found.

Code so far is:
function main(workbook: ExcelScript.Workbook) {

let selectedSheet = workbook.getActiveWorksheet();
let archive = workbook.getWorksheet("Archive");

let cell = selectedSheet.getRange("A1");
let cell2 = selectedSheet.getRange("A2");

// Read the value from the source cell
let cellValue = cell.getValue();
let cell2Value = cell2.getValue();

// Store the source cell value as a variable
let myVariable = cellValue;
let myVariable2 = cell2Value;

archive.getRange("4:19").insert(ExcelScript.InsertShiftDirection.down);
archive.getRange("4:19").copyFrom(selectedSheet.getRange("120:135"));

/// have tried using cell, cellValue, myVariable in place of 120 and 135, but get message argument is invalid or has incorrect format???

}

So the values above that say "120:135" will be some variable, maybe one of the variables above, but I keep getting errors when I place a variable in place of the 120 or 135.

Thanks for any help, have been trying all types of prompts and ways to get it to work for days, just dont know how to convert a variable for that Range.

[HansV]

Welcome to Eileen's Lounge!

I know very little about Office Script. but does this do what you want?

Code: Select all

function main(workbook: ExcelScript.Workbook) {

    let selectedSheet = workbook.getActiveWorksheet();
    let archive = workbook.getWorksheet("Archive");

    let cell = selectedSheet.getRange("A1");
    let cell2 = selectedSheet.getRange("A2");

    // Read the value from the source cell
    let cellValue = cell.getValue();
    let cell2Value = cell2.getValue();

    // Store the source cell value as a variable
    let myVariable = cellValue;
    let myVariable2 = cell2Value;
    let numRows = myVariable2 - myVariable + 1;
    let lastRow = 4 + numRows - 1;
    let dstAddress = "4:" + lastRow.toString();
    let srcAddress = myVariable.toString() + ":" + myVariable2.toString();

    archive.getRange(dstAddress).insert(ExcelScript.InsertShiftDirection.down);
    archive.getRange(dstAddress).copyFrom(selectedSheet.getRange(srcAddress));

}

[BrainStain]

"Smooth As Silk" HansV

I needed to walk it thru line-by-line on paper to see what to expect since its a very populated Excel file, and now I understand a little bit more how your coding (new to me) works. I didn't need to change a thing, and went to other user sheets to test it out, even some jobs that were down to rows 250 and it pulled those in Extremely well and copied them over to the Archive sheet with grace.

Ive been playing with how to do this for over a month, and lately solidly for the past week, and was lost, and your answer provided me with how to move forward the rest of the way, to remove it from the source once its Archived.

Thank you very much HansV for this help.

[BobH]

Welcome to Eileen's Lounge, BrainStain!

If you stay here long you will find that HansV is a marvel and in may different subject areas.

Come back soon!

[snb]

In VBA:
In sheet1, A1: the starting row number
15 rows to transfer to another location.

Code: Select all

Sub M_snb()
  Sheet1.Rows(Sheet1.Cells(1)).Resize(15).Cut
  Sheet2.Rows(1).Resize(15).Insert -4121
End Sub

You can adapt sheet2 by a sheet in any open workbook.

[BrainStain]

thanks snb,

I will consider that if I have any troubles with the first option,
so far its been working, but its good to know a backup plan.

Always have a plan B, right.