PDA

View Full Version : Budget Workbook - Code Help



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

xld
08-02-2018, 11:34 AM
Try this


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Month As Integer

Month = Range("G2").Value

'Food-------------------------------------------------------------

If Target.Address = "$F$15" Then

If Month >= 1 And Month <= 12 Then

ThisWorkbook.Sheets("Personal Budget").Cells(32, Month + 1).Value = Range("F15").Value
End If
End If
End Sub

Rob342
06-06-2019, 07:01 AM
Much easier way

Use Microsoft money you can download it for free it will do all you it to

Rob