Confirm and reset - Help! So I recently made a couple posts on here on how to do

Question

Confirm and reset - Help!

So I recently made a couple posts on here on how to do this, however, I recently changed the workbook slightly and had to change the VBA, though, when attempting to do so I COMPLETELY FAILED! So if you guys would help me fix it I would greatly appreciate it.

I would like to make that when I put a certain month into a cell the values of certain formulas turn into permanent values.

Data Information:

Cell to put month in = L6

Cells to make formulas in values (depending on value of L6) -

August = E17:K17

September = E18:K18

October = E19:K19

November = E20:K20

December = E21:K21

January = E22:K22

February = E23:K23

March =E24:K24

April = E25:K25

May = E26:K26

June = E27:K27

July = E28:K28

Example: I put August in cell L6... all formulas in range E17:K17 turn into the formulas value... so if E17's formula was equal to 19, cell E17 is now 19 instead of a formula, and so on.

In addition, I would like I would like to revert this change if ever necessary, I would like to make that when I put a certain month into a cell the values of some cells are replaced by formulas in other cells.

Data Information:

Cell to put month in = L8

Cells to make values into formulas (depending on value of L8) -

August = E17:K17

September = E18:K18

October = E19:K19

November = E20:K20

December = E21:K21

January = E22:K22

February = E23:K23

March =E24:K24

April = E25:K25

May = E26:K26

June = E27:K27

July = E28:K28

Cells to get formulas from to put into the cells above -

August = E36:K36

September = E37:K37

October = E38:K38

November = E39:K39

December = E40:K40

January = E41:K41

February = E42:K42

March = E43:K43

April = E44:K44

May = E45:K45

June = E46:K46

July = E47:K47

Example: I put August into cell L8, all formulas from range E36:K36 are copied to range E17:K17... so if E36 was =A1 then now E17 is now also =A1, however, E36 was never messed with, it was just used to retrieve the formula.

I am currently using this VBA:

Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Planner").Unprotect Password:=""
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("L6, L8")) Is Nothing Then
On Error GoTo FallThrough
Application.EnableEvents = False
Dim lMM As Long
If IsDate(Target.Value) Then
lMM = Month(Target.Value)
ElseIf IsNumeric(Target.Value) Then
lMM = CLng(Target.Value)
Else
lMM = Month(DateValue(Target.Text & " 2014"))
End If
With Cells(lMM + 7 - (lMM < 8) * 11, 5).Resize(1, 7)
If Target.Address(0, 0) = "L6" Then
.Value = .Value
.Font.Bold = True
.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
ElseIf Target.Address(0, 0) = "L8" Then
.Formula = .Offset(19, 0).Formula
.Font.Bold = False
.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
End If
End With
Range("L6:M6, L8:M8").ClearContents
End If
FallThrough:
Application.EnableEvents = True
Sheets("Planner").Protect Password:=""
End Sub

I understand that it all works through offsetting cells, however, I just have no idea how to fix it, and in reality ended up completely ruining it. I made that when I put "August" it changes "February" and so on. PLEASE HELP, I would greatly appreciate if someone could please fix this for me or at least, if possible, make a more comprehensible VBA that has the same function, cell formatting, but fixed cells locations.

THANK YOU!

Explanation / Answer

I think your basic error is that the row math should be changed from

With Cells(lMM + 7 - (lMM < 8) * 11, 5).Resize(1, 7)

to

With Cells(lMM + 9 - (lMM < 8) * 12, 5).Resize(1, 7)

Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Planner").Unprotect Password:=""
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("L6, L8")) Is Nothing Then
On Error GoTo FallThrough
Application.EnableEvents = False
Dim lMM As Long
If IsDate(Target.Value) Then
lMM = Month(Target.Value)
ElseIf IsNumeric(Target.Value) Then
lMM = CLng(Target.Value)
Else
lMM = Month(DateValue(Target.Text & " 2014"))
End If
With Cells(lMM + 9 - (lMM < 8) * 12, 5).Resize(1, 7)
If Target.Row = 6 Then
.Value = .Value
.Font.Bold = True
.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Else
.Formula = .Offset(19, 0).Formula
.Font.Bold = False
.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
End If
End With
Range("L6:M6, L8:M8").ClearContents
End If
FallThrough:
Application.EnableEvents = True
Sheets("Planner").Protect Password:=""
End Sub

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.