LookItsMe
08-02-2018, 09:28 AM
Hi,
This is my first post here after getting back into the world of VBA to help make life easier.
I am making a budget planner to help when I move into a new flat. This planner contains all my incomes and expenses, I have created a long IF statement which checks which month we are in and inputs these figures into the main budget from the working area.
This is the code I currently have which is not really the most easily readable. I was wondering if there is an easier way to get to the same conclusion.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Month As Integer
Month = Range("G2").Value
'Food-------------------------------------------------------------
If Target.Address = "$F$15" And Month = "1" Then 'January
ThisWorkbook.Sheets("Personal Budget").Range("B32") = Range("F15").Value
ElseIf Target.Address = "$F$15" And Month = "2" Then 'Febuary
ThisWorkbook.Sheets("Personal Budget").Range("C32") = Range("F15").Value
ElseIf Target.Address = "$F$15" And Month = "3" Then 'March
ThisWorkbook.Sheets("Personal Budget").Range("D32") = Range("F15").Value
ElseIf Target.Address = "$F$15" And Month = "4" Then 'April
ThisWorkbook.Sheets("Personal Budget").Range("E32") = Range("F15").Value
ElseIf Target.Address = "$F$15" And Month = "5" Then 'May
ThisWorkbook.Sheets("Personal Budget").Range("F32") = Range("F15").Value
ElseIf Target.Address = "$F$15" And Month = "6" Then 'June
ThisWorkbook.Sheets("Personal Budget").Range("JG32") = Range("F15").Value
ElseIf Target.Address = "$F$15" And Month = "7" Then 'July
ThisWorkbook.Sheets("Personal Budget").Range("H32") = Range("F15").Value
ElseIf Target.Address = "$F$15" And Month = "8" Then 'August
ThisWorkbook.Sheets("Personal Budget").Range("I32") = Range("F15").Value
ElseIf Target.Address = "$F$15" And Month = "9" Then 'September
ThisWorkbook.Sheets("Personal Budget").Range("J32") = Range("F15").Value
ElseIf Target.Address = "$F$15" And Month = "10" Then 'October
ThisWorkbook.Sheets("Personal Budget").Range("K32") = Range("F15").Value
ElseIf Target.Address = "$F$15" And Month = "11" Then 'November
ThisWorkbook.Sheets("Personal Budget").Range("L32") = Range("F15").Value
ElseIf Target.Address = "$F$15" And Month = "12" Then 'Dcember
ThisWorkbook.Sheets("Personal Budget").Range("M32") = Range("F15").Value
End If
End Sub
Any assistance will be a great help as I will not need to copy and paste this for each of the different expense sections.
Luke
This is my first post here after getting back into the world of VBA to help make life easier.
I am making a budget planner to help when I move into a new flat. This planner contains all my incomes and expenses, I have created a long IF statement which checks which month we are in and inputs these figures into the main budget from the working area.
This is the code I currently have which is not really the most easily readable. I was wondering if there is an easier way to get to the same conclusion.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Month As Integer
Month = Range("G2").Value
'Food-------------------------------------------------------------
If Target.Address = "$F$15" And Month = "1" Then 'January
ThisWorkbook.Sheets("Personal Budget").Range("B32") = Range("F15").Value
ElseIf Target.Address = "$F$15" And Month = "2" Then 'Febuary
ThisWorkbook.Sheets("Personal Budget").Range("C32") = Range("F15").Value
ElseIf Target.Address = "$F$15" And Month = "3" Then 'March
ThisWorkbook.Sheets("Personal Budget").Range("D32") = Range("F15").Value
ElseIf Target.Address = "$F$15" And Month = "4" Then 'April
ThisWorkbook.Sheets("Personal Budget").Range("E32") = Range("F15").Value
ElseIf Target.Address = "$F$15" And Month = "5" Then 'May
ThisWorkbook.Sheets("Personal Budget").Range("F32") = Range("F15").Value
ElseIf Target.Address = "$F$15" And Month = "6" Then 'June
ThisWorkbook.Sheets("Personal Budget").Range("JG32") = Range("F15").Value
ElseIf Target.Address = "$F$15" And Month = "7" Then 'July
ThisWorkbook.Sheets("Personal Budget").Range("H32") = Range("F15").Value
ElseIf Target.Address = "$F$15" And Month = "8" Then 'August
ThisWorkbook.Sheets("Personal Budget").Range("I32") = Range("F15").Value
ElseIf Target.Address = "$F$15" And Month = "9" Then 'September
ThisWorkbook.Sheets("Personal Budget").Range("J32") = Range("F15").Value
ElseIf Target.Address = "$F$15" And Month = "10" Then 'October
ThisWorkbook.Sheets("Personal Budget").Range("K32") = Range("F15").Value
ElseIf Target.Address = "$F$15" And Month = "11" Then 'November
ThisWorkbook.Sheets("Personal Budget").Range("L32") = Range("F15").Value
ElseIf Target.Address = "$F$15" And Month = "12" Then 'Dcember
ThisWorkbook.Sheets("Personal Budget").Range("M32") = Range("F15").Value
End If
End Sub
Any assistance will be a great help as I will not need to copy and paste this for each of the different expense sections.
Luke