Generating Payslip 2

[gnod]

Hi,

if i didn't put "x" in column A, it will generate payslip for all plate#.. (this is correct)

but

if i put "x" in column A, that plate# will generate a payslip for the selected plate# but the alignment is not in proper position. the second plate # should be in Row 1. (from left to right).

this is a continuation from my previous post.. (thanks XLD)

[gnod]

i already solve my problem :p ..

i change the baseRow formula under ELSE
from

baseRow = ((i - 5) \ 2) * 16 + 1
to

baseRow = ((NumToPrint - j) \ 2) * 16 + 1

here's the complete sub procedure of GeneratePayslip,


' Generate Payslip
Sub GeneratePayslip()
Const TEST_COLUMN As String = "B" '<=== change to suit
Dim i As Long, j As Long, NumToPrint As Long
Dim iLastRow As Long
Dim col As Long
Dim baseRow As Long
Dim aryheads
aryheads = Array("Service Fee", "Fuel", "Toll / Parking Fee", "", _
"DEDUCTIONS:", "Maintenance Fund", "Cash Bond", _
"Fuel Payment", "Short Remittance", "", "Net Payment")
ClearPayslip2
Application.ScreenUpdating = False
With Worksheets("Data")
iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
col = 4
With Worksheets("Payslip")
NumToPrint = WorksheetFunction.CountA(Range("ToPrint"))
j = NumToPrint
If j = 0 Then
For i = 5 To iLastRow
col = col - (col = 1) * 3 + (col = 4) * 3
baseRow = ((i - 5) \ 2) * 16 + 1
If baseRow > 1 And baseRow Mod 48 = 1 And col = 1 Then
.HPageBreaks.Add Before:=.Cells(baseRow, "A")
End If
.Cells(baseRow, col).Value = Worksheets("Data").Range("B3").Value
.Cells(baseRow, col).Font.Bold = True
.Cells(baseRow + 1, col).Value = Worksheets("Data").Cells(i, "B").Value
.Cells(baseRow + 3, col).Resize(11) = Application.Transpose(aryheads)
.Cells(baseRow + 1, col).Interior.ColorIndex = 36
.Cells(baseRow + 7, col).Font.Bold = True
.Cells(baseRow + 13, col).Font.Italic = True
.Cells(baseRow + 1, col + 1).FormulaR1C1 = "=VLOOKUP(R" & baseRow + 1 & "C" & col & ",Data!C2:C3,2,FALSE)"
.Cells(baseRow + 1, col + 1).HorizontalAlignment = xlCenter
.Cells(baseRow + 3, col + 1).FormulaR1C1 = "=SUMIF(Data_PlateNum,R" & baseRow + 1 & "C" & col & ",Data_ServiceFee)"
.Cells(baseRow + 4, col + 1).FormulaR1C1 = "=SUMIF(Data_PlateNum,R" & baseRow + 1 & "C" & col & ",Data_Fuel)"
.Cells(baseRow + 5, col + 1).FormulaR1C1 = "=SUMIF(Data_PlateNum,R" & baseRow + 1 & "C" & col & ",Data_TollParkingFee)"
.Cells(baseRow + 8, col + 1).FormulaR1C1 = "=SUMIF(Data_PlateNum,R" & baseRow + 1 & "C" & col & ",Data_MaintenanceFund)"
.Cells(baseRow + 9, col + 1).FormulaR1C1 = "=SUMIF(Data_PlateNum,R" & baseRow + 1 & "C" & col & ",Data_CashBond)"
.Cells(baseRow + 10, col + 1).FormulaR1C1 = "=SUMIF(Data_PlateNum,R" & baseRow + 1 & "C" & col & ",Data_FuelPayment)"
.Cells(baseRow + 11, col + 1).FormulaR1C1 = "=SUMIF(Data_PlateNum,R" & baseRow + 1 & "C" & col & ",Data_Shortunremitted)"
.Cells(baseRow + 13, col + 1).FormulaR1C1 = "=SUM(R" & baseRow + 3 & "C:R" & baseRow + 5 & "C)-SUM(R" & baseRow + 8 & "C:R" & baseRow + 11 & "C)"
.Cells(baseRow + 13, col + 1).Font.Bold = True
With .Range(.Cells(baseRow, col), .Cells(baseRow + 13, col + 1))
With .Borders(xlEdgeLeft)
.LineStyle = xlDashDot
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlDashDot
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlDashDot
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlDashDot
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
Next I
Else
For i = 5 To iLastRow
If Worksheets("Data").Cells(i, "A").Value = "x" Then
col = col - (col = 1) * 3 + (col = 4) * 3
baseRow = ((NumToPrint - j) \ 2) * 16 + 1
If baseRow > 1 And baseRow Mod 48 = 1 And col = 1 Then
.HPageBreaks.Add Before:=.Cells(baseRow, "A")
End If
.Cells(baseRow, col).Value = Worksheets("Data").Range("B3").Value
.Cells(baseRow, col).Font.Bold = True
.Cells(baseRow + 1, col).Value = Worksheets("Data").Cells(i, "B").Value
.Cells(baseRow + 3, col).Resize(11) = Application.Transpose(aryheads)
.Cells(baseRow + 1, col).Interior.ColorIndex = 36
.Cells(baseRow + 7, col).Font.Bold = True
.Cells(baseRow + 13, col).Font.Italic = True
.Cells(baseRow + 1, col + 1).FormulaR1C1 = "=VLOOKUP(R" & baseRow + 1 & "C" & col & ",Data!C2:C3,2,FALSE)"
.Cells(baseRow + 1, col + 1).HorizontalAlignment = xlCenter
.Cells(baseRow + 3, col + 1).FormulaR1C1 = "=SUMIF(Data_PlateNum,R" & baseRow + 1 & "C" & col & ",Data_ServiceFee)"
.Cells(baseRow + 4, col + 1).FormulaR1C1 = "=SUMIF(Data_PlateNum,R" & baseRow + 1 & "C" & col & ",Data_Fuel)"
.Cells(baseRow + 5, col + 1).FormulaR1C1 = "=SUMIF(Data_PlateNum,R" & baseRow + 1 & "C" & col & ",Data_TollParkingFee)"
.Cells(baseRow + 8, col + 1).FormulaR1C1 = "=SUMIF(Data_PlateNum,R" & baseRow + 1 & "C" & col & ",Data_MaintenanceFund)"
.Cells(baseRow + 9, col + 1).FormulaR1C1 = "=SUMIF(Data_PlateNum,R" & baseRow + 1 & "C" & col & ",Data_CashBond)"
.Cells(baseRow + 10, col + 1).FormulaR1C1 = "=SUMIF(Data_PlateNum,R" & baseRow + 1 & "C" & col & ",Data_FuelPayment)"
.Cells(baseRow + 11, col + 1).FormulaR1C1 = "=SUMIF(Data_PlateNum,R" & baseRow + 1 & "C" & col & ",Data_Shortunremitted)"
.Cells(baseRow + 13, col + 1).FormulaR1C1 = "=SUM(R" & baseRow + 3 & "C:R" & baseRow + 5 & "C)-SUM(R" & baseRow + 8 & "C:R" & baseRow + 11 & "C)"
.Cells(baseRow + 13, col + 1).Font.Bold = True
With .Range(.Cells(baseRow, col), .Cells(baseRow + 13, col + 1))
With .Borders(xlEdgeLeft)
.LineStyle = xlDashDot
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlDashDot
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlDashDot
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlDashDot
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
j = j - 1
End If
Next I
End If
.PageSetup.TopMargin = Application.InchesToPoints(0.5)
.PageSetup.BottomMargin = Application.InchesToPoints(0)
.PageSetup.LeftMargin = Application.InchesToPoints(0)
.PageSetup.RightMargin = Application.InchesToPoints(0)
.PageSetup.CenterHorizontally = True
.Columns("A").ColumnWidth = 16
.Columns("B").ColumnWidth = 20
.Columns("C").ColumnWidth = 3
.Columns("D").ColumnWidth = 16
.Columns("E").ColumnWidth = 20
.Columns("B").NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* "" - ""??_);_(@_)"
.Columns("E").NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* "" - ""??_);_(@_)"
End With
End With
Application.ScreenUpdating = True
MsgBox "Generate Payslip.. Done", vbInformation, "OTO Payroll"
End Sub


Thanks to me :thumb

[Aussiebear]

Gnod, you're a dead set legend... from my view of the world.

[Bob Phillips]

The logic here seems odd to me. In the second loop you decrement j, but as far as I can see you never use it.

Why would you not have pagebreaks if j is zero?

[gnod]

The logic here seems odd to me. In the second loop you decrement j, but as far as I can see you never use it.

Why would you not have pagebreaks if j is zero?

i use the variable j in

baseRow - ((NumToPrint - j) \ 2) * 16 + 1

instead of

baseRow - ((i - 5) \ 2) * 16 + 1

i only need to count the "x" if the user wants to generate based on their selection..

[gnod]

Gnod, you're a dead set legend... from my view of the world.

what do you mean by dead set legend? :think: