I am confused why the same code which works in one file is not working in another
Previous File
Column L contains the Gross Pay amounts (Cell Format = Currency")
Value of items in Column L obtained by simple multiplication of the values of 2 cells in the worksheet.
Any suggestions would be most welcome
New File
Column M contains the Gross Pay amounts (Cell Format = Currency")
Value of items in Column M uploaded to cells in Currency format
NOTE: The SUMIFS works for the txtPayableHoursWork & txtPayableHoursLeave lines of code
New File
Dropbox
Previous File
Column L contains the Gross Pay amounts (Cell Format = Currency")
Value of items in Column L obtained by simple multiplication of the values of 2 cells in the worksheet.
VBA Code:
Private Sub cmdPayMonthSearch_Click()
txtMonthlyPayMonth.Value = cboPayMonthSearch.Value
txtMonthlyWorkHours = Format(WorksheetFunction.SumIfs(Columns("J"), Columns("B"), "Work", Columns("D"), cboPayMonthSearch.Value), "#,##0.00")
txtMonthlyWorkEarningsGross = Format(WorksheetFunction.SumIfs(Columns("L"), Columns("B"), "Work", Columns("D"), cboPayMonthSearch.Value), "£#,##0.00")
txtMonthlyLeaveHours = Format(WorksheetFunction.SumIfs(Columns("J"), Columns("B"), "Leave", Columns("D"), cboPayMonthSearch.Value), "#,##0.00")
txtMonthlyLeaveEarningsGross = Format(WorksheetFunction.SumIfs(Columns("L"), Columns("B"), "Leave", Columns("D"), cboPayMonthSearch.Value), "£#,##0.00")
Dim x As Double, y As Double
x = txtMonthlyWorkEarningsGross.Value
y = txtMonthlyLeaveEarningsGross.Value
txtTotalMonthlyEarningsGross.Value = Format(x + y, "Currency")
End Sub
New File
Column M contains the Gross Pay amounts (Cell Format = Currency")
Value of items in Column M uploaded to cells in Currency format
VBA Code:
Private Sub cmdMonthSearch_Click()
txtPayMonthMonth.Value = cboSearchByPayMonth.Value
txtPayableHoursWork = Format(WorksheetFunction.SumIfs(Columns("K"), Columns("D"), "Work", Columns("C"), cboSearchByPayMonth.Value), "#,##0.00")
txtGrossPayWork = Format(WorksheetFunction.SumIfs(Columns("M"), Columns("D"), "Work", Columns("C"), cboSearchByPayMonth.Value), "£#,##0.00")
txtPayableHoursLeave = Format(WorksheetFunction.SumIfs(Columns("K"), Columns("D"), "Leave", Columns("C"), cboSearchByPayMonth.Value), "#,##0.00")
txtGrossPayLeave = Format(WorksheetFunction.SumIfs(Columns("M"), Columns("D"), "Leave", Columns("C"), cboSearchByPayMonth.Value), "£#,##0.00")
Dim x As Double, y As Double
x = txtGrossPayWork.Value
y = txtGrossPayLeave.Value
txtTotalGrossPay.Value = Format(x + y, "Currency")
Set wsPayPeriods = ThisWorkbook.Worksheets("Pay Periods")
txtPayDate.Text = Application.WorksheetFunction.VLookup(cboSearchByPayMonth.Text, Sheet5.Range("A2:D30"), 2, False)
End Sub
NOTE: The SUMIFS works for the txtPayableHoursWork & txtPayableHoursLeave lines of code
New File
Dropbox