Hello,
Please could someone help. I have no idea how to edit it to get it to do what I want. If I fill down the formulas, the offset won't work because there is already numbers in the V column
Here is my(someone wrote this for me originally) code:
Sub RectangleRoundedCorners2_Click()
Dim rng As Range
Set rng = Range("V" & Cells(Rows.Count, "V").End(xlUp).Row)
rng.Offset(1, 0) = [K15]
rng.Offset(1, 1) = [J2]
rng.Offset(1, 2) = [J3]
rng.Offset(2, 0) = [V1].Copy
rng.Offset(2, 0) = [V1].pastespecial xlpasteformulas
End Sub
It won't copy the formula V1, I want it to act like FILL SERIES which updatee the cell references when I copy the formula.
Please could someone help. I have no idea how to edit it to get it to do what I want. If I fill down the formulas, the offset won't work because there is already numbers in the V column
Here is my(someone wrote this for me originally) code:
Sub RectangleRoundedCorners2_Click()
Dim rng As Range
Set rng = Range("V" & Cells(Rows.Count, "V").End(xlUp).Row)
rng.Offset(1, 0) = [K15]
rng.Offset(1, 1) = [J2]
rng.Offset(1, 2) = [J3]
rng.Offset(2, 0) = [V1].Copy
rng.Offset(2, 0) = [V1].pastespecial xlpasteformulas
End Sub
It won't copy the formula V1, I want it to act like FILL SERIES which updatee the cell references when I copy the formula.
Ningbo Carriage Checker - EC - SEP21.xlsm | ||||||||
---|---|---|---|---|---|---|---|---|
V | W | X | Y | Z | AA | |||
1 | FALSE | Pallet Size | Postcode | S | Amount | Price increase | ||
2 | £49.80 | Q | G | £49.80 | £49.80 | |||
3 | £49.80 | F | TN | £49.80 | £49.80 | |||
4 | £44.51 | Q | ng | Y | £44.51 | £44.51 | ||
5 | FALSE | FALSE | £0.00 | |||||
6 | £0.00 | £0.00 | ||||||
7 | £0.00 | £0.00 | ||||||
8 | £0.00 | £0.00 | ||||||
9 | £0.00 | £0.00 | ||||||
10 | £0.00 | £0.00 | ||||||
11 | £0.00 | £0.00 | ||||||
12 | £0.00 | £0.00 | ||||||
13 | £0.00 | £0.00 | ||||||
14 | £0.00 | £0.00 | ||||||
15 | £0.00 | £0.00 | ||||||
16 | £0.00 | £0.00 | ||||||
17 | £0.00 | £0.00 | ||||||
18 | £0.00 | £0.00 | ||||||
19 | £0.00 | £0.00 | ||||||
20 | £0.00 | £0.00 | ||||||
21 | £0.00 | £0.00 | ||||||
22 | £0.00 | £0.00 | ||||||
23 | £0.00 | £0.00 | ||||||
24 | £0.00 | £0.00 | ||||||
25 | £0.00 | £0.00 | ||||||
26 | £0.00 | £0.00 | ||||||
27 | £0.00 | £0.00 | ||||||
28 | £0.00 | £0.00 | ||||||
29 | £0.00 | £0.00 | ||||||
30 | £0.00 | £0.00 | ||||||
31 | £0.00 | £0.00 | ||||||
32 | £0.00 | £0.00 | ||||||
33 | £0.00 | £0.00 | ||||||
34 | £0.00 | £0.00 | ||||||
35 | Total | £144.11 | £144.11 | |||||
Checker |
Cell Formulas | ||
---|---|---|
Range | Formula | |
Z2:Z34 | Z2 | =V2 |
AA2:AA34 | AA2 | =IFERROR((Z2*$AB$1)+(Z2)," ") |
Z35:AA35 | Z35 | =SUM(Z2:Z34) |
V1:V2 | V1 | =IF(Y1="Y",V1,IF(W1=0," ",(IF($J$1="Yes",IF(W1="Q",(INDEX($P$1:$S$439,MATCH(X1,$P:$P,0),2)),IF(W1="H",(INDEX($P$1:$S$439,MATCH(X1,$P:$P,0),3)),IF(W1="F",(INDEX($P$1:$S$439,MATCH(X1,$P:$P,0),4))))),IF($J$1="No",IF(W1="Q",(INDEX($A$1:$D$593,MATCH(X1,$A:$A,0),2)),IF(W1="H",(INDEX($A$1:$D$593,MATCH(X1,$A:$A,0),3)),IF(W1="F",(INDEX($A$1:$D$593,MATCH(X1,$A:$A,0),4)))))))))) |
V3 | V3 | =IF(Y3="Y",V3,IF(W3=0," ",(IF($J$1="Yes",IF(W3="Q",(INDEX($P$1:$S$439,MATCH(X3,$P:$P,0),2)),IF(W3="H",(INDEX($P$1:$S$439,MATCH(X3,$P:$P,0),3)),IF(W3="F",(INDEX($P$1:$S$439,MATCH(X3,$P:$P,0),4))))),IF($J$1="No",IF(W3="Q",(INDEX(A2:$D$593,MATCH(X3,$A:$A,0),2)),IF(W3="H",(INDEX(A2:$D$593,MATCH(X3,$A:$A,0),3)),IF(W3="F",(INDEX(A2:$D$593,MATCH(X3,$A:$A,0),4)))))))))) |
V5 | V5 | =IF(Y1="Y",V1,IF(W1=0," ",(IF($J$1="Yes",IF(W1="Q",(INDEX($P$1:$S$439,MATCH(X1,$P:$P,0),2)),IF(W1="H",(INDEX($P$1:$S$439,MATCH(X1,$P:$P,0),3)),IF(W1="F",(INDEX($P$1:$S$439,MATCH(X1,$P:$P,0),4))))),IF($J$1="No",IF(W1="Q",(INDEX($A$1:$D$593,MATCH(X1,$A:$A,0),2)),IF(W1="H",(INDEX($A$1:$D$593,MATCH(X1,$A:$A,0),3)),IF(W1="F",(INDEX($A$1:$D$593,MATCH(X1,$A:$A,0),4)))))))))) |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
_FilterDatabase | =Checker!$A$1:$D$1 | V5, V1:V3 |