WPS--表格-JS宏-JSA--打开智能筛选后，写值异常

如下有一表格

智能筛选打开，将带有底纹的单元格隐藏（通过智能筛选的方式隐藏，正常的隐藏不受影响）

执行如下宏指令后

function test(){

let range1=Worksheets("Sheet1").UsedRange.Value2;

for(i=0;i<range1.length;i++){

for(j=0;j<range1[0].length;j++){

range1[i][j]=100*i+j;

}

}

Worksheets("Sheet1").UsedRange.Value2=range1;

}

此仍为筛选状态

解除筛选后，可以看出写入区域被分为三个子区域，分别对这三个区域写入了数组中的内容

我们在非筛选状态下，再次执行对应代码

可以看出，写入值正常

对区域单元格中写入值前可以使用 解除筛选状态

Worksheets("Sheet1").AutoFilterMode = false;

猜测：

打开录制宏，手动执行筛选操作

function Macro1()

{

Selection.AutoFilter(undefined, undefined, xlAnd, undefined, undefined);

Rows.Item("1:1").Select();

Selection.AutoFilter(undefined, undefined, xlAnd, undefined, undefined);

ActiveWorkbook.Names.Add("Sheet1!_FilterDatabase", "=Sheet1!$A$1:$J$1", false, undefined, undefined, undefined, undefined, undefined, undefined, undefined, undefined);

Range("A1:J10").AutoFilter(6, RGB(4, 0, 2), xlFilterCellColor, undefined, undefined);

ActiveWorkbook.Names.Add("Sheet1!_FilterDatabase", "=Sheet1!$A$1:$J$10", false, undefined, undefined, undefined, undefined, undefined, undefined, undefined, undefined);

Range("F3").Select();

Range("A1:J10").AutoFilter(6, RGB(4, 0, 2), xlFilterCellColor, undefined, undefined);

ActiveWorkbook.Names.Add("Sheet1!_FilterDatabase", "=Sheet1!$A$1:$J$10", false, undefined, undefined, undefined, undefined, undefined, undefined, undefined, undefined);

Range("F2").Select();

Range("A1:J10").AutoFilter(6, RGB(4, 0, 2), xlFilterCellColor, undefined, undefined);

ActiveWorkbook.Names.Add("Sheet1!_FilterDatabase", "=Sheet1!$A$1:$J$10", false, undefined, undefined, undefined, undefined, undefined, undefined, undefined, undefined);

Range("M18").Select();

Selection.AutoFilter(undefined, undefined, xlAnd, undefined, undefined);

Range("M5").Select();

}

可以看出，每次在进行筛选操作，都会对当前工作表添加一个区域，创建别名，索引工作表的单元格时，会受到上述机制影响，从而造成预期外的值写入。