Consulting

Results 1 to 2 of 2

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 xld's Avatar
    Joined
    Apr 2005
    Posts
    24,967
    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

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
  •