咨询一个公式引用变化和正则表达式Replace的函数使用

panglong00

目前有1个EXCEL模板文件，复制到其他EXCEL目标文件后，单元格公式中的本簿引用，会自动变成模板文件簿引用，我的目标是复制后公式不自动变  or  变后的公式能修正为原公式。



如：   
模板文件中某单元格的公式是：
=SUMIF('1.1 工程审核汇总对比表'!B:B,B3,'1.1 工程审核汇总对比表'!C:C)


复制后变为
=SUMIF('d:\我的文档\桌面\[模板-审核说明-建设片区新小区雨污混接改造工程20230420.xlsm]1.1 工程审核汇总对比表'!B:B,B3,'d:\我的文档\桌面\[模板-审核说明-建设片区新小区雨污混接改造工程20230420.xlsm]1.1 工程审核汇总对比表'!C:C)


需要修改回 or 让公式保持不变：  
=SUMIF('1.1 工程审核汇总对比表'!B:B,B3,'1.1 工程审核汇总对比表'!C:C)


另外，下面这个报错，
sht.UsedRange.Formula = .Replace(sht.UsedRange.Formula, "")       '这句目前报错，需要修正
请问一下能帮助解决一下吗？


文件在附件中。

taller

taller

sht.UsedRange.Formula = sht.UsedRange.Value

zpy2

这样子比较麻烦，最好数据保存在数据库，展示时抽取

panglong00

taller 发表于 2023-5-2 00:48
sht.UsedRange.Formula = sht.UsedRange.Values

谢谢版主，问题处理了一半。

正则这句   '[^!]*?]    OK了。

但是

sht.UsedRange.Formula = sht.UsedRange.Values   这个目前用不起来。

依旧报错。
我尝试了，添加一个变量。赋值时问题依旧
ss = [A1].Formula       这句OK    ，SS正则后， [A1].Formula=ss   也OK  

但是
ss = sht.UsedRange.Formula   这句报错

目前不清楚为什么，还请帮助一下。谢谢。

准提部林

1) 複製工作表之前, 先將表內 "=" 號都先替換為 "AAA=" ...使為文本
2) 移動至目標活頁簿後, 再將表內 "AAA=" 替換回 "＝"...恢復公式

__但要確認公式引用的工作表, 在目標活頁簿是存在的

panglong00

准提部林 发表于 2023-5-2 14:44
1) 複製工作表之前, 先將表內 "=" 號都先替換為 "AAA=" ...使為文本
2) 移動至目標活頁簿後, 再將表內 "AA ...

谢谢，这个方法不是我需要的。   

我需要的是VBA自动处理。

准提部林

panglong00 发表于 2023-5-2 15:03
谢谢，这个方法不是我需要的。   

我需要的是VBA自动处理。

那就是VBA的方法, 不是要你手動....
with 本檔活頁簿.sheets(??)
       .usedrange.replace "=", "AAA=", Lookat:=xlpart
       .copy ~~ 至~~目標活頁簿
end with
目標活頁簿.activesheet.usedrange.replace "AAA=", "=", Lookat:=xlpart

panglong00

准提部林 发表于 2023-5-2 15:24
那就是VBA的方法, 不是要你手動....
with 本檔活頁簿.sheets(??)
       .usedrange.replace "=", "AAA ...

谢谢！
在家研究了1天，基本解决了。
usedrange.formula 无法赋值成功，所以采用了range.formula赋值，同时把修正范围缩小到具体数值如：[A1：AA11]

这样基本一个for each就OK了。

再次感谢提点！

准提部林

做個簡例//執行時, 須先手動開啟BookB
Test_CopySheet.rar (9.61 KB, 下载次数: 9)

2023-5-4 10:27 上传

点击文件名下载附件

公式用樓上的取代法, 還是不一定可行,
因檔案名稱及工作表名稱的文字不同, 公式中不一定會有[單引號]~~