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 |
