|
这样改一下,就会不会把数值替换掉了:
Sub test()
rq0 = CStr(Month(Now())) & "." & CStr(Day(Now())) '当前日期 0
rq1 = Split(Sheets("简版").[B4].Formula, "'")(1) '从简版表公式中找到当前最后一个表名 1
rq2 = Split(Sheets(rq1).[I8].Formula, "'")(1) '从最后一个表的公式中找到引用的前一个个表名 2
If rq0 = rq1 Then
MsgBox "当前表已是最新日期,无需重新生成!"
Exit Sub
Else
Set sh = ThisWorkbook.Sheets(rq1)
sh.Copy Before:=sh
Set sht = ActiveSheet
sht.Name = rq0
sht.[A2] = "填表时间:" & Month(Now()) & "月" & Day(Now()) & "日"
For Each cell In sht.UsedRange
If cell.HasFormula Then
If InStr(1, cell.Formula, rq2) > 0 Then cell.Formula = Replace(cell.Formula, rq2, rq1)
End If
Next
For Each cell In Sheets("简版").UsedRange
If cell.HasFormula Then
If InStr(1, cell.Formula, rq1) > 0 Then cell.Formula = Replace(cell.Formula, rq1, rq0)
If InStr(1, cell.Formula, "#REF") > 0 Then cell.Formula = Replace(cell.Formula, "#REF", rq0)
End If
Next
Sheets("简版").[A2] = "填表时间:" & Month(Now()) & "月" & Day(Now()) & "日"
ll = CInt(Split(rq0, ".")(1))
Sheets("出租率").Cells(20, ll + 1) = sht.[B7]
Sheets("出租率").Cells(21, ll + 1) = sht.[B7] / 1499
End If
MsgBox "OK!"
End Sub |
评分
-
1
查看全部评分
-
|