How do I assign variables

K

KevinM

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)
 
P

Per Jessen

Hi

First, an integer variable can only hold integer values. As the result
of your calculation may result in a decimal value, declare the
resulting variable as Double.

Second, I am not sure what you are trying to do with the statement
below....

paycode1=Cells(j, 12).Formula = Cells(j, 7) * Cells(j, 11)

Hopes this helps.
....
Per
 
F

FSt1

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
 
K

KevinM

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)
 
N

norie

Kevin

Why do you have all these variables?

Why all the if statements?

I really think you need to look into both of these - for a start you
appear to be giving the same value to multiple variables and perhaps
you could use Select Case instead of all those Ifs.

Perhaps something like this for the start of the code, I didn't go
through it all - my fingers were starting to hurt.:)

Select Case Cells(j, 6).Value
Case 1, 12, 13
Cells(j, 12).Formula = Cells(j, 7) * Cells(j, 11)
Case "1E"
paycode1E = Cells(j, 12).Formula = Cells(j, 7) * Cells(j, 11)
Case "1H"
paycode1H = Cells(j, 12).Formula = Cells(j, 7) * Cells(j, 11)
Case "1I"
paycode1I = Cells(j, 12).Formula = Cells(j, 7) * Cells(j, 11)
Case "1J"
paycode1J = Cells(j, 12).Formula = Cells(j, 7) * Cells(j, 11)
Case "1M"
paycode1M = Cells(j, 12).Formula = Cells(j, 7) * Cells(j, 11)
Case "1V"
paycode1V = Cells(j, 12).Formula = Cells(j, 7) * Cells(j, 11)
Case "1N"
paycode1N = Cells(j, 12).Formula = Cells(j, 7) * Cells(j, 11)
Case "7B"
earnings7b = Cells(j, 10)
Case "7C"
earnings7C = Cells(j, 10)
Case "7D"
earnings7D = Cells(j, 10)
Case "7E"
earnings7E = Cells(j, 10)
Case "7M"
earnings7M = Cells(j, 10)
Case "7Q"
earnings7Q = Cells(j, 10)
Case "7S"
earnings7S = Cells(j, 10)
Case "7Z"
earnings7Z = Cells(j, 10)
Case "99"
earnings99 = Cells(j, 10)
Case "9A"
earnings9A = Cells(j, 10)
Case "9B"
earnings9B = Cells(j, 10)
Case "9C"
earnings9C = Cells(j, 10)
Case "9D"
earnings9D = Cells(j, 10)
Case "9F"
earnings9F = Cells(j, 10)
Case "9G"
earnings9G = Cells(j, 10)
Case "9H"
earnings9H = Cells(j, 10)
Case "9L"
earnings9L = Cells(j, 10)
Case "9O"
earnings9O = Cells(j, 10)
Case "9P"
earnings9P = Cells(j, 10)
Case "9S"
earnings9S = Cells(j, 10)
Case "9T"
earnings9T = Cells(j, 10)
Case "9U"
earnings9U = Cells(j, 10)
End Select
 
K

KevinM

Nori: Hi. I didn't know if these variables were necessary. I do know
however I did need some kind of variables because later in the VBA program I
need to take the results and add then divide certain paycodes. I'm open to
any suggestions because I'm new at this and just read resource books on VBA
and went with it. Thanks again for the help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top