0

I have code that references a sheet set to variable within the macro.

Unless I am phsyically on the "Income and Expenses" sheet in the workbook when the macro runs, I get the Object Out of Range Error message for any line that references PnL_Sheet.

Any idea why this is or if I am setting it incorrectly?

Code:

Set PnL_Sheet = ThisWorkbook.Worksheets("Income & Expenses")


With PnL_Sheet.Range(Cells(StartRow + 1, FirstCol), Cells(LastRow, LastCol))
    .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* "" - ""??_);_(@_)"
End With

Debug.Print JobStartDate
PnL_Sheet.Cells(2, 1) = JobStartDate

'Salary and Wages Income
Dim SalaryWagesLine As Integer
SalaryWagesLine = 5

For i = FirstCol To LastCol
    If InStr(1, PnL_Sheet.Cells(StartRow, i).Value, "FY") Or InStr(1, PnL_Sheet.Cells(StartRow, i).Value, "YTD") Then
            PnL_Sheet.Cells(SalaryWagesLine, i) = Application.Sum(PnL_Sheet.Range(Cells(SalaryWagesLine, i - MonthCounter), Cells(SalaryWagesLine, i - 1)))
            MonthCounter = 0
    ElseIf PnL_Sheet.Cells(StartRow, i).Value >= JobStartDate Then
        If salaried Then
            PnL_Sheet.Cells(SalaryWagesLine, i) = salary / 12 * ((1 + IncomeRaiseRate) ^ (Year(PnL_Sheet.Cells(StartRow, i).Value) - Year(Date)))
            MonthCounter = MonthCounter + 1
        ElseIf hourly Then
            PnL_Sheet.Cells(SalaryWagesLine, i) = monthly_hours * wage_rate * ((1 + IncomeRaiseRate) ^ (Year(PnL_Sheet.Cells(StartRow, i).Value) - Year(Date)))
            MonthCounter = MonthCounter + 1
        End If
    End If
Next i

0 Answers0