该公式通过此VBA代码给出excel中的值，但下面公式中的范围是修复：
Private Sub Formula update()
Dim rng As Range
Dim rng1 As Range
Set rng = Range("B1").End(xlDown)
rng.Offset(1, 0).Formula = "=TEXT(RIGHT(**B9**,9)+3,""DDMMMYYYY"")&"" - ""&TEXT(RIGHT(*B9*,9)+7,""DDMMMYYYY"")"
rng.Offset(1, -1).Formula = "=CONCATENATE(""WK-"",IF((WEEKNUM(LEFT(**B10**,9),2)-40)<=0,(WEEKNUM(LEFT(**B10**,9),2)-40)+53,WEEKNUM(LEFT(**B10**,9),2)-40))"
End Sub
但是在下面提到的代码中，我需要公式下的动态范围 .
有人可以解决它 .
Private Sub FormulaValue_paste()
Dim rng As Range
Dim rng1 As Range
Set rng = Range("B1").End(xlDown)
rng.Offset(1, 0).Formula = "=TEXT(RIGHT(rng,9)+3,""DDMMMYYYY"")&"" - ""&TEXT(RIGHT(rng,9)+7,""DDMMMYYYY"")"
Set rng1 = rng.Offset(1, 0)
rng.Offset(1, -1).Formula = "=CONCATENATE(""WK-"",IF((WEEKNUM(LEFT(rng1,9),2)-40)<=0,(WEEKNUM(LEFT(rng1,9),2)-40)+53,WEEKNUM(LEFT(rng1,9),2)-40))"
End Sub
在此先感谢您的支持 .
1 回答
我猜，但如果你的意思是你想让公式引用rng1，也许就是这个 . 使用范围的Address属性并将其从引号中删除 .