I have a mcro that converts a spreadsheet into organized data (the way they want it). One of the functions is that it has to figure up the wage per an hour based on different factors. Hours, overtime, milage (input via msg box), and base pay(hard coded in macro). It works great! However, now I have to figure up how much to charge each location based on the number of hours spent there, the code used (ie job function) and the persons wage (which we just figured out above). If I do not add in milage(just hit ok), it will find the location and code fine on the worksheet and place the correct amount in the cell. If I input any milage, it will not find the location correctly and inputs in to a different Cell. I have triple checked the code and can see no place of how this should effect the output. Even the variant is correct to show the value, but the cell.find messes up.
I have the locations setup as general on the sheet as some of them start with a 0 and excel kept removing it and then none of the finds worked. So far this has been working good till this.
of intrest: location 025 will set in location 620 and location 185 will set in 261.
The spreadsheet can be found at :
I have the locations setup as general on the sheet as some of them start with a 0 and excel kept removing it and then none of the finds worked. So far this has been working good till this.
of intrest: location 025 will set in location 620 and location 185 will set in 261.
The spreadsheet can be found at :
Code:
Sub Newpayment()
Dim emp1hours
Dim emp2hours
Dim emp3hours
Dim emp4hours
Dim emp5hours
Dim emp6hours
Dim emp7hours
Dim emp8hours
Dim emp1othours
Dim emp2othours
Dim emp3othours
Dim emp4othours
Dim emp5othours
Dim emp6othours
Dim emp7othours
Dim emp8othours
Dim emp1milage
Dim emp2milage
Dim emp3milage
Dim emp4milage
Dim emp5milage
Dim emp6milage
Dim emp7milage
Dim emp8milage
Dim emp1rate
Dim emp2rate
Dim emp3rate
Dim emp4rate
Dim emp5rate
Dim emp6rate
Dim emp7rate
Dim emp8rate
Dim property
Dim propnumber
Dim codenumber
Dim codehours
Dim name
Application.ScreenUpdating = False
Sheets("finish").Select
Range("AQ:AQ").Select
Selection.NumberFormat = "$#,##0.00"
Range("a1").Select
emp1hours = 160
emp1othours = 17.25
emp2hours = 100.75
emp2othours = 0
emp3hours = 104.25
emp3othours = 0
emp4hours = 142
emp4othours = .5
emp5hours = 150
emp5othours = 4.75
emp6hours = 3.25
emp6othours = 0
emp7hours = 104.75
emp7othours = 0
emp8hours = 122
emp8othours = 0
emp1tothours = emp1hours + emp1othours
emp2tothours = emp2hours + emp2othours
emp3tothours = emp3hours + emp3othours
emp4tothours = emp4hours + emp4othours
emp5tothours = emp5hours + emp5othours
emp6tothours = emp6hours + emp6othours
emp7tothours = emp7hours + emp7othours
emp8tothours = emp8hours + emp8othours
emp1milage = InputBox("Please enter total mileage for Employee 1 this month.", "Employee 1’s Milage")
If emp1milage = "" Then
Emp1milage = "0"
End If
Emp2milage = InputBox("Please enter total mileage for Employee 2 this month.", "Employee 2’s Milage")
If emp2milage = "" Then
Emp2milage = "0"
End If
Emp3milage = InputBox("Please enter total mileage for Employee 3 this month.", "Employee 3’s Milage")
If emp3milage = "" Then
Emp3milage = "0"
End If
Emp4milage = InputBox("Please enter total mileage for Employee 4 this month.", "Employee 4’s Milage")
If emp4milage = "" Then
Emp4milage = "0"
End If
Emp5milage = InputBox("Please enter total mileage for Employee 5 this month.", "Employee 5’s Milage")
If emp5milage = "" Then
Emp5milage = "0"
End If
Emp6milage = InputBox("Please enter total mileage for Employee 6 this month.", "Employee 6’s Milage")
If emp6milage = "" Then
Emp6milage = "0"
End If
Emp7milage = InputBox("Please enter total mileage for Employee 7 this month.", "Employee 7’s Milage")
If emp7milage = "" Then
Emp7milage = "0"
End If
Emp8milage = InputBox("Please enter total mileage for Employee 8 this month.", "Employee 8’s Milage")
If emp8milage = "" Then
Emp8milage = "0"
End If
Range("AQ8").Value = Application.WorksheetFunction.Sum((emp1milage + (1.75 * ((emp1hours * 16.06) + (emp1othours * (24.09))))) / emp1tothours)
Range("AQ9").Value = Application.WorksheetFunction.Sum((emp2rmilage + (1.75 * ((emp2hours * 16.06) + (emp2othours * 24.09)))) / emp4tothours)
Range("AQ10").Value = Application.WorksheetFunction.Sum((emp3milage + (1.75 * ((emp3hours * 12.71) + (emp3othours * 19.06)))) / emp3tothours)
Range("AQ11").Value = Application.WorksheetFunction.Sum((emp4milage + (1.75 * ((emp4hours * 12.25) + (emp4othours * 18.37)))) / emp4tothours)
Range("AQ12").Value = Application.WorksheetFunction.Sum((emp5milage + (1.75 * ((emp5hours * 14.3) + (emp5othours * 21.45)))) / emp5tothours)
Range("AQ13").Value = Application.WorksheetFunction.Sum((emp6milage + (1.75 * ((emp6hours * 8.5) + (emp6othours * 12.75)))) / emp6tothours)
Range("AQ14").Value = Application.WorksheetFunction.Sum((emp7milage + (1.75 * ((emp7hours * 11) + (emp7othours * 16.5)))) / emp7tothours)
Range("AQ15").Value = Application.WorksheetFunction.Sum((emp8milage + (1.75 * ((emp8hours * 10.6) + (emp8othours * 15.9)))) / emp8tothours)
emp1rate = Range("AQ8").Value
emp2rate = Range("AQ9").Value
emp3rate = Range("AQ10").Value
emp4rate = Range("AQ11").Value
emp5rate = Range("AQ12").Value
emp6rate = Range("AQ13").Value
emp7rate = Range("AQ14").Value
emp8rate = Range("AQ15").Value
Sheets.add Type:="Worksheet"
With ActiveSheet
.name = "Property"
End With
Range("A:A").Select
Selection.NumberFormat = "@"
Range("A2").Select
ActiveCell.FormulaR1C1 = "011"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "013"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "024"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "025"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "035"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "041"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "051"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "075"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "083"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "111"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "140"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "141"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "142"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "143"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "171"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "180"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "181"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "182"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "184"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "185"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "190"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "261"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "268"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "471"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "512"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "611"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "612"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "613"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "620"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "621"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "622"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "630"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "98200"
Range("C1:M1").Select
Selection.NumberFormat = "@"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Total"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "6500"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "7200"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "7210"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "7220"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "7300"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "7310"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "7320"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "7330"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "7340"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "7400"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "7410"
Range("B2:m35").Select
Selection.NumberFormat = "$#,##0.00"
Sheets("tempproperty").Select
Range("c:c").Select
Selection.NumberFormat = "@"
'cuts by name to seperate
namerng = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
For N = 1 To namerng
name = Range("A1").Value
On Error Resume Next
mylastrow = Cells.Find(name, [A1], , , xlByRows, xlPrevious).Row
mylastcolumn = Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
mylastcell = Cells(mylastrow, mylastcolumn).Address
myrange = "a1:" & mylastcell
Range(myrange).Select
Selection.cut
Sheets.add Type:="Worksheet"
With ActiveSheet
.name = name
End With
Range("A1").Select
ActiveSheet.Paste
'sort by property
With Selection
.sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
End With
Range("a1").Select
If Range("a1").Value = "Josh Hoversten" Then
Call josh
Else
End If
If Range("a1").Value = "Tracy Nowlin" Then
Call tracy
Else
End If
'cut by property, make into loop for all properties.
proprng = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
For p = 1 To proprng
propnumber = Range("C1").Value
mylastrow = Cells.Find(propnumber, [A1], , , xlByRows, xlPrevious).Row
mylastcolumn = Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
mylastcell = Cells(mylastrow, mylastcolumn).Address
myrange = "a1:" & mylastcell
Range(myrange).Select
Selection.cut
Sheets.add Type:="Worksheet"
With ActiveSheet
.name = "propsheet"
End With
Range("A1").Select
ActiveSheet.Paste
'sort by codes
With Selection
.sort Key1:=Range("D1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
End With
'sort by codes
coderng = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
For c = 1 To coderng
codenumber = Range("D1").Value
On Error Resume Next
mylastrow = Cells.Find(codenumber, [A1], , , xlByRows, xlPrevious).Row
mylastcolumn = Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
mylastcell = Cells(mylastrow, mylastcolumn).Address
myrange = "a1:" & mylastcell
Range(myrange).Select
Selection.cut
Sheets.add Type:="Worksheet"
With ActiveSheet
.name = "code"
End With
Range("A1").Select
ActiveSheet.Paste
With Selection
Range("F1").Value = Application.WorksheetFunction.Sum([b:b])
End With
If Range("A1").Value = "Employee1" Then
codehours = Range("F1").Value * Emp1rate
Else
End If
If Range("A1").Value = " Employee2" Then
codehours = Range("F1").Value * Emp2rate
Else
End If
If Range("A1").Value = " Employee3" Then
codehours = Range("F1").Value * Emp3rate
Else
End If
If Range("A1").Value = " Employee4" Then
codehours = Range("F1").Value * Emp4rate
Else
End If
If Range("A1").Value = " Employee5" Then
codehours = Range("F1").Value * Emp5rate
Else
End If
If Range("A1").Value = " Employee6" Then
codehours = Range("F1").Value * Emp6brate
Else
End If
If Range("A1").Value = " Employee7" Then
codehours = Range("F1").Value * Emp7rate
Else
End If
If Range("A1").Value = " Employee8" Then
codehours = Range("F1").Value * emp8rate
Else
End If
Sheets("Property").Select
Range("a1").Select
mylastrow = Cells.Find(propnumber, [A1], , , xlByRows, xlPrevious).Row
mylastcolumn = Cells.Find(codenumber, [A1], , , xlByColumns, xlPrevious).Column
mylastcell = Cells(mylastrow, mylastcolumn).Address
Range(mylastcell).Select
If Range(mylastcell).Value <> "" Then
Range(mylastcell).Value = Range(mylastcell).Value + codehours
Else
ActiveCell.FormulaR1C1 = codehours
End If
Application.DisplayAlerts = False
Sheets("code").Delete
Application.DisplayAlerts = True
Sheets("propsheet").Select
With Application
.Calculation = xlCalculationManual
'We work backwards because we are deleting rows.
For j = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(j)) = 0 Then
Selection.Rows(j).EntireRow.Delete
End If
Next j
End With
If Range("A1").Value = "" Then
Exit For
Else
End If
Next c
Application.DisplayAlerts = False
Sheets("propsheet").Delete
Application.DisplayAlerts = True
Sheets(name).Select
With Application
.Calculation = xlCalculationManual
'We work backwards because we are deleting rows.
For j = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(j)) = 0 Then
Selection.Rows(j).EntireRow.Delete
End If
Next j
End With
If Range("A1").Value = "" Then
Exit For
Else
End If
Next p
Application.DisplayAlerts = False
Sheets(name).Delete
Application.DisplayAlerts = True
Sheets("tempproperty").Select
With Application
.Calculation = xlCalculationManual
'We work backwards because we are deleting rows.
For j = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(j)) = 0 Then
Selection.Rows(j).EntireRow.Delete
End If
Next j
End With
If Range("A1").Value = "" Then
Exit For
Else
End If
Next N
Sheets("Property").Select
Range("C2:N2").Select
Range("N2").Activate
ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
Range("C3:N3").Select
Range("N3").Activate
ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
Range("C4:N4").Select
Range("N4").Activate
ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
Range("C5:N5").Select
Range("N5").Activate
ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
Range("C6:N6").Select
Range("N6").Activate
ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
Range("C7:N7").Select
Range("N7").Activate
ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
Range("C8:N8").Select
Range("N8").Activate
ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
Range("C9:N9").Select
Range("N9").Activate
ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
Range("C10:N10").Select
Range("N10").Activate
ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
Range("C11:N11").Select
Range("N11").Activate
ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
Range("C12:N12").Select
Range("N12").Activate
ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
Range("C13:N13").Select
Range("N13").Activate
ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
Range("C14:N14").Select
Range("N14").Activate
ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
Range("C15:N15").Select
Range("N15").Activate
ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
Range("C16:N16").Select
Range("N16").Activate
ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
Range("C17:N17").Select
Range("N17").Activate
ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
Range("C18:N18").Select
Range("N18").Activate
ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
Range("C19:N19").Select
Range("N19").Activate
ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
Range("C20:N20").Select
Range("N20").Activate
ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
Range("C21:N21").Select
Range("N21").Activate
ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
Range("C22:N22").Select
Range("N22").Activate
ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
Range("C23:N23").Select
Range("N23").Activate
ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
Range("C24:N24").Select
Range("N24").Activate
ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
Range("C25:N25").Select
Range("N25").Activate
ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
Range("C26:N26").Select
Range("N26").Activate
ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
Range("C27:N27").Select
Range("N27").Activate
ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
Range("C28:N28").Select
Range("N28").Activate
ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
Range("C29:N29").Select
Range("N29").Activate
ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
Range("C30:N30").Select
Range("N30").Activate
ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
Range("C31:N31").Select
Range("N31").Activate
ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
Range("C32:N32").Select
Range("N32").Activate
ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
Range("C33:N33").Select
Range("N33").Activate
ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
Range("C34:N34").Select
Range("N34").Activate
ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
Range("C35:N35").Select
Range("N35").Activate
ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
Range("N2:N35").Select
Selection.cut
Range("B2").Select
ActiveSheet.Paste
Range("A1").Select
Application.DisplayAlerts = False
Sheets("tempproperty").Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Sheets("finish").Select
End Sub