Thanks for the reply. The code seems to be working as its placing the values
in the row and columns I want. What I'm trying to do is search for a paycode
value of 1 in row J(my For and Next J) colunm 6 and once it finds that value
take the same row and multiply the value in column K with the value in
column G and put the result in column 12 of the same row.
Does that make sense? Is there a better way to do it? I'm just trying to put
a value not a formula. I think you're getting syntax errors because I only
gave you a snippet of the code. I also placed variables in front of my
formulas but its not working.
Again, I appreciate your help. I'm trying to find out if there's a better
way to do this as this is my first attempt at writing VBA.
Heres the entire code:
Sub calculate_retropay()
Dim paycode12 As Integer
Dim paycode13 As Integer
Dim paycode1E As Integer
Dim paycode1H As Integer
Dim paycode1I As Integer
Dim paycode1J As Integer
Dim paycode1 As Integer
'this will search for the pay period in column C
Enterpp = InputBox("Please enter the period number ie 2008-21-0")
finalrow = Cells(65536, 3).End(xlUp).Row
finalrow1 = Cells(65536, 6).End(xlUp).Row
'finalrow = Cells(65536, 10).End(xlUp).Row
For i = 1 To finalrow
For j = 1 To finalrow1
'takes the value of what pay period is keyed in
If Cells(i, 3).Value = Enterpp Then
'if paycode =1 then take the new rate * by hours
If Cells(j, 6).Value = 1 Then
Cells(j, 12).Formula = Cells(j, 7) * Cells(j, 11)
'Else
'If Cells(j, 6).Value = 5 Then
'Cells(j, 12).Value = "test"
Else
'if paycode =12 then take the new rate * by hours
If Cells(j, 6).Value = 12 Then
Cells(j, 12).Formula = Cells(j, 7) * Cells(j, 11)
Else
'if paycode =13 then take the new rate * by hours
If Cells(j, 6).Value = 13 Then
Cells(j, 12).Formula = Cells(j, 7) * Cells(j, 11)
Else
'if paycode =1E then take the new rate * by hours
If Cells(j, 6).Value = "1E" Then
paycode1E = Cells(j, 12).Formula = Cells(j, 7) * Cells(j, 11)
Else
'if paycode =1H then take the new rate * by hours
If Cells(j, 6).Value = "1H" Then
paycode1H = Cells(j, 12).Formula = Cells(j, 7) * Cells(j, 11)
Else
'if paycode =1I then take the new rate * by hours
If Cells(j, 6).Value = "1I" Then
paycode1I = Cells(j, 12).Formula = Cells(j, 7) * Cells(j, 11)
Else
If Cells(j, 6).Value = "1J" Then
paycode1J = Cells(j, 12).Formula = Cells(j, 7) * Cells(j, 11)
Else
'if paycode =1M then take the new rate * by hours
If Cells(j, 6).Value = "1M" Then
paycode1M = Cells(j, 12).Formula = Cells(j, 7) * Cells(j, 11)
Else
'if paycode =1V then take the new rate * by hours
If Cells(j, 6).Value = "1V" Then
paycode1V = Cells(j, 12).Formula = Cells(j, 7) * Cells(j, 11)
Else
'if paycode =1N then take the new rate * by hours
If Cells(j, 6).Value = "1N" Then
paycode1N = Cells(j, 12).Formula = Cells(j, 7) * Cells(j, 11)
End If
'grabbing the earnings
If Cells(j, 6).Value = "7B" Then
earnings7b = Cells(j, 10).Select
Else
If Cells(j, 6).Value = "7C" Then
earnings7C = Cells(j, 10).Select
Else
If Cells(j, 6).Value = "7D" Then
earnings7D = Cells(j, 10).Select
Else
If Cells(j, 6).Value = "7E" Then
earnings7E = Cells(j, 10).Select
Else
If Cells(j, 6).Value = "7M" Then
earnings7M = Cells(j, 10).Select
Else
If Cells(j, 6).Value = "7Q" Then
earnings7Q = Cells(j, 10).Select
Else
If Cells(j, 6).Value = "7S" Then
earnings7S = Cells(j, 10).Select
Else
If Cells(j, 6).Value = "7Z" Then
earnings7Z = Cells(j, 10).Select
Else
If Cells(j, 6).Value = 99 Then
earnings99 = Cells(j, 10).Select
Else
If Cells(j, 6).Value = "9A" Then
earnings9A = Cells(j, 10).Select
Else
If Cells(j, 6).Value = "9B" Then
earnings9B = Cells(j, 10).Select
Else
If Cells(j, 6).Value = "9C" Then
earnings9C = Cells(j, 10).Select
Else
If Cells(j, 6).Value = "9D" Then
earnings9D = Cells(j, 10).Select
Else
If Cells(j, 6).Value = "9F" Then
earnings9F = Cells(j, 10).Select
Else
If Cells(j, 6).Value = "9G" Then
Cells(j, 10).Select
earnings9G = ActiveCell.Value
Else
If Cells(j, 6).Value = "9H" Then
earnings9H = Cells(j, 10).Select
Else
If Cells(j, 6).Value = "9L" Then
earnings9L = Cells(j, 10).Select
Else
If Cells(j, 6).Value = "9O" Then
earnings9O = Cells(j, 10).Select
Else
If Cells(j, 6).Value = "9P" Then
earnings9P = Cells(j, 10).Select
Else
If Cells(j, 6).Value = "9S" Then
earnings9S = Cells(j, 10).Select
Else
If Cells(j, 6).Value = "9T" Then
earnings9T = Cells(j, 10).Select
Else
If Cells(j, 6).Value = "9U" Then
earnings9U = Cells(j, 10).Select
End If
'defining hours
If Cells(j, 6).Value = 1 Then
hours1 = Cells(j, 7).Select
Else
If Cells(j, 6).Value = 12 Then
hours12 = Cells(j, 7).Select
Else
If Cells(j, 6).Value = 13 Then
hours13 = Cells(j, 7).Select
Else
If Cells(j, 6).Value = "1E" Then
hours1E = Cells(j, 7).Select
Else
If Cells(j, 6).Value = "1H" Then
hours1H = Cells(j, 7).Select
Else
If Cells(j, 6).Value = "1L" Then
hours1L = Cells(j, 7).Select
Else
If Cells(j, 6).Value = "1J" Then
hours1J = Cells(j, 7).Select
Else
If Cells(j, 6).Value = "1M" Then
hours1M = Cells(j, 7).Select
Else
If Cells(j, 6).Value = "1V" Then
hours1V = Cells(j, 7).Select
Else
If Cells(j, 6).Value = "1N" Then
Cells(j, 7).Select
End If
If Cells(j, 6).Value = 5 Then
Cells(j, 12).Value = "this is a test"
Columns("M:M").NumberFormat = "0.0000"
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
Next j
Next i
End Sub
FSt1 said:
hi
what are we trying to do with this line....
paycode1 = Cells(j, 12).Formula = Cells(j, 7) * Cells(j, 11)
it seems you are trying to put value in paycode1 AND put a formula for
that
value in cells(j, 12). cant do that
are you trying to put a formula in the cell above or just a value?
paycode1 = cells(j, 7).value * Cells(j, 11).value
cells(j, 12).value = paycode1.value
or for formula
cells(j, 12).FormulaR1C1 = "=RC[-5]+RC[-1]"
post back with more detail as to what you are trying to do with this line
because it has syntax error all over it.
Regards
FSt1
KevinM said:
Hi. I'm new to VBA and wrote this code to calculate pay. It's working
but
what I also need is to have calculations done by taking the data and
adding
them and then dividing them. I wanted to assign varibles to the IF
statements to allow me make the addition and division easier but its not
working. How do I assign variables to allow me to add and divide? Here
is
the code. Thank you in advance.
Sub calculate_retropay()
Dim paycode12 As Integer
Dim paycode13 As Integer
Dim paycode1E As Integer
Dim paycode1H As Integer
Dim paycode1I As Integer
Dim paycode1J As Integer
Dim paycode1 As Integer
'this will search for the pay period in column C
Enterpp = InputBox("Please enter the period number ie 2008-21-0")
finalrow = Cells(65536, 3).End(xlUp).Row
finalrow1 = Cells(65536, 6).End(xlUp).Row
For i = 1 To finalrow
For j = 1 To finalrow1
'takes the value of what pay period is keyed in
If Cells(i, 3).Value = Enterpp Then
'if paycode =1 then take the new rate * by hours
If Cells(j, 6).Value = 1 Then
paycode1=Cells(j, 12).Formula = Cells(j, 7) * Cells(j, 11)
***when I assign this as a variable it doesn't work
Else
If Cells(j, 6).Value = 5 Then
Cells(j, 12).Value = "test" ** if its set like
this
when it works fine
Else
if paycode =12 then take the new rate * by hours
If Cells(j, 6).Value = 12 Then
Cells(j, 12).Formula = Cells(j, 7) * Cells(j, 11)
