hsandeep
Well-known Member
- Joined
- Dec 6, 2008
- Messages
- 1,226
- Office Version
- 2010
- Platform
- Windows
- Mobile
X1, Y1 and Z1 are time value
H5, L5, P5 and T5=10 (by default)
H9:H16=10; L9:L16=10; P9:P16=10; T9:T16=10 (by default)
Vba code required for below actions
I have 1 incomplete code
H5, L5, P5 and T5=10 (by default)
H9:H16=10; L9:L16=10; P9:P16=10; T9:T16=10 (by default)
Vba code required for below actions
- IF X1<now()<=Y1 Then
- IF $B$2<=F5<=$B$1, Then IF F5 HasFormula Then Remove formula from F5 AND insert value 20 in $H$5. Also, Then IF F9:F16 HasFormula Then Remove formula from F9:F16 AND insert value 20 in H9:H16 (column H is 2 columns on the RHS of column F). Also, IF $B$2<=J5<=$B$1, Then IF J5 HasFormula Then Remove formula from J5 AND insert value 20 in $L$5. Also, Then IF J9:J16 HasFormula Then Remove formula from J9:J16 AND insert value 20 in L9:L16 (column L is 2 columns on the RHS of column J). Also, IF $B$2<=N5<=$B$1, Then IF N5 HasFormula Then Remove formula from N5 AND insert value 20 in $P$5. Also, Then IF N9:N16 HasFormula Then Remove formula from N9:N16 AND insert value 20 in P9:P16 (column P is 2 columns on the RHS of column N). Also, IF $B$2<=R5<=$B$1, Then IF R5 HasFormula Then Remove formula from R5 AND insert value 20 in $T$5. Also, Then IF R9:R16 HasFormula Then Remove formula from R9:R16 AND insert value 20 in T9:T16 (column T is 2 columns on the RHS of column R).
- IF X1<now()<=Z1 Then
- IF F5<=$B$3 AND H5=20, Then insert the formula back in F5 in R1C1 style AND fill H5 with 10 AND also insert the formula back in F9:F16 in R1C1 style AND fill H9:H16 with 10. Also, IF J5<=$B$3 AND L5=20, Then insert the formula back in J5 in R1C1 style AND fill L5 with 10 AND also insert the formula back in J9:J16 in R1C1 style AND fill L9:L16 with 10. Also, IF N5<=$B$3 AND P5=20, Then insert the formula back in N5 in R1C1 style AND fill P5 with 10 AND also insert the formula back in N9:N16 in R1C1 style AND fill P9:P16 with 10. Also, IF R5<=$B$3 AND T5=20, Then insert the formula back in R5 in R1C1 style AND fill T5 with 10 AND also insert the formula back in R9:R16 in R1C1 style AND fill T9:T16 with 10.
- IF X1<now()<=Z1 Then
- IF F5>$B$3 AND H5=10, Then Remove formula from F5 AND fill H5 with 20 AND also Remove formula from F9:F16 AND fill H9:H16 with 20. Also, IF J5>$B$3 AND L5=10, Then Remove formula from J5 AND fill L5 with 20 AND also Remove formula from J9:J16 AND fill L9:L16 with 20. Also, IF N5>$B$3 AND P5=10, Then Remove formula from N5 AND fill P5 with 20 AND also Remove formula from N9:N16 AND fill P9:P16 with 20. Also, IF R5>$B$3 AND T5=10, Then Remove formula from R5 AND fill T5 with 20 AND also Remove formula from R9:R16 AND fill T9:T16 with 20.
- IF Z1<=now() Then
- Insert the formula back in F5, J5, N5 and R5 in R1C1 style. Also, insert the formula back in F9:F16; J9:J16; N9:N16; R9:R16 in R1C1 style. Also fill H5 with 10, L5 with 10, P5 with 10 and T5 with 10. Also fill H9:H16 with 10; L9:L16 with 10; P9:P16 with 10 and T9:T16 with 10.
Copy vba.xlsm | |||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | |||
1 | 40250 | 03/13/2023 9:30:00 AM | 03/13/2023 9:31:00 AM | 03/13/2023 3:30:00 PM | |||||||||||||||||||||||
2 | 40101 | ||||||||||||||||||||||||||
3 | 40228 | 40100 | 40200 | 40300 | 40400 | ||||||||||||||||||||||
4 | |||||||||||||||||||||||||||
5 | 40100 | 40100 | 10 | 40200 | 40200 | 20 | 40300 | 40300 | 10 | 40400 | 40400 | 10 | |||||||||||||||
6 | |||||||||||||||||||||||||||
7 | |||||||||||||||||||||||||||
8 | |||||||||||||||||||||||||||
9 | 39900 | 800 | 800 | 800 | 10 | 800 | 800 | 20 | 800 | 800 | 10 | 800 | 800 | 10 | |||||||||||||
10 | 40000 | 750 | 750 | 750 | 10 | 750 | 750 | 20 | 750 | 750 | 10 | 750 | 750 | 10 | |||||||||||||
11 | 40100 | 630 | 630 | 630 | 10 | 630 | 630 | 20 | 630 | 630 | 10 | 630 | 630 | 10 | |||||||||||||
12 | 40200 | 600 | 600 | 600 | 10 | 600 | 600 | 20 | 600 | 600 | 10 | 600 | 600 | 10 | |||||||||||||
13 | 40300 | 532 | 532 | 532 | 10 | 532 | 532 | 20 | 532 | 532 | 10 | 532 | 532 | 10 | |||||||||||||
14 | 40400 | 463 | 463 | 463 | 10 | 463 | 463 | 20 | 463 | 463 | 10 | 463 | 463 | 10 | |||||||||||||
15 | 40500 | 302 | 302 | 302 | 10 | 302 | 302 | 20 | 302 | 302 | 10 | 302 | 302 | 10 | |||||||||||||
16 | 40600 | 253 | 253 | 253 | 10 | 253 | 253 | 20 | 253 | 253 | 10 | 253 | 253 | 10 | |||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F3,R3,N3,J3 | F3 | =F5 |
F5,R9:R16,N9:N16,J9:J16,F9:F16,R5,N5 | F5 | =G5 |
G9:G16 | G9 | =D9 |
K9:K16 | K9 | =D9 |
O9:O16 | O9 | =D9 |
S9:S16 | S9 | =D9 |
I have 1 incomplete code
Rich (BB code):
Sub CheckAndUpdateValues()
Dim nowTime As Date
nowTime = Now()
Dim startTime As Date
Dim endTime As Date
startTime = Range("X1").Value
endTime = Range("Y1").Value
If startTime < nowTime And nowTime <= endTime Then
If Range("F5").Value >= Range("$B$2").Value And Range("F5").Value <= Range("$B$1").Value And Range("F5").HasFormula Then
Range("F5").Value = 20
Range("H5").Value = 20
For i = 9 To 16
If Range("F" & i).HasFormula Then
Range("F" & i).Value = 20
Range("H" & i).Value = 20
End If
Next i
End If
If Range("J5").Value >= Range("$B$2").Value And Range("J5").Value <= Range("$B$1").Value And Range("J5").HasFormula Then
Range("J5").Value = 20
Range("L5").Value = 20
For i = 9 To 16
If Range("J" & i).HasFormula Then
Range("J" & i).Value = 20
Range("L" & i).Value = 20
End If
Next i
End If
If Range("N5").Value >= Range("$B$2").Value And Range("N5").Value <= Range("$B$1").Value And Range("N5").HasFormula Then
Range("N5").Value = 20
Range("P5").Value = 20
For i = 9 To 16
If Range("N" & i).HasFormula Then
Range("N" & i).Value = 20
Range("P" & i).Value = 20
End If
Next i
End If
If Range("R5").Value >= Range("$B$2").Value And Range("R5").Value <= Range("$B$1").Value And Range("R5").HasFormula Then
Range("R5").Value = 20
Range("T5").Value = 20
For i = 9 To 16
If Range("R" & i).HasFormula Then
Range("R" & i).Value = 20
Range("T" & i).Value = 20
End If
Next i
End If
End If
startTime = Range("X1").Value
endTime = Range("Z1").Value
If startTime < nowTime And nowTime <= endTime Then
If Range("F5").Value <= Range("$B$3").Value And Range("H5").Value = 20 And Range("F5").HasFormula = False Then
Range("F5").FormulaR1C1 = Range("F5").FormulaR1C1
Range("H5").Value = 10
For i = 9 To 16
If Range("F" & i).HasFormula = False Then
Range("F" & i).FormulaR1C1 = Range("F" & i).FormulaR1C1
Range("H" & i).Value = 10
End If
Next i
End If
If Range("J5").Value <= Range("$B$3").Value And Range("L5").Value = 20 And Range("J5").HasFormula = False Then
Range("J5").FormulaR1C1 = Range("J5").FormulaR1C1
Range("L5").Value = 10
For i = 9 To 16
If Range("J" & i).HasFormula = False Then
Range("J" & i).FormulaR1C1 = Range("J" & i).FormulaR1C1
Range("L" & i).Value = 10
End If
Next i
End If
If Range("N5").Value <= Range("$B$3").Value And Range("P5").Value = 20 And Range("N5").HasFormula = False Then
Range("N5").FormulaR1C1 = Range("N5").FormulaR1C1
Range("P5").Value = 10
For i = 9 To 16
If Range("N" & i).HasFormula = False Then
Range("N" & i).FormulaR1C1 = Range("N" & i).FormulaR1C1
Range("P" & i).Value = 10
End If
Next i
End If
If Range("R5").Value <= Range("$B$3").Value And Range("T5").Value = 20 And Range("R5").HasFormula = False Then
Range("R5").FormulaR1C1 = Range("R5").FormulaR1C1
Range("T5").Value = 10
For i = 9 To 16
If Range("R" & i).HasFormula = False Then
Range("R" & i).FormulaR1C1 = Range("R" & i).FormulaR1C1
Range("T" & i).Value = 10
End If
Next i
End If