Consulting

Results 1 to 3 of 3

Thread: Budget Workbook - Code Help

  1. #1

    Question Budget Workbook - Code Help

    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

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,438
    Location
    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
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Mentor
    Joined
    Apr 2009
    Location
    Kingsbury
    Posts
    423
    Location
    Much easier way

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

    Rob

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •