Formula problem in code
 

Im just learning this and have almost no clue what Im doing.
I have the following code in my worksheet. It 'seems' to be working
fine except the formulas I have between the Emoticons. Right now I have
the proper functions I would like in there but, D2 should be Column D,
E2 should be Column E, F2 should be Column F, and etc.. Each row is
going to have the same formulas in them columns. Also it would help if
it wouldnt display "#N/A" or "#NAME?" when data is not there. And 1
more thing, If I put a employee ID# that doesnt exist, it still puts
info in.

I put an example .xls here, It will probably give you a better idea:


http://www.ashleylandscaping.com/sample.xls


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim wsPay As Worksheet, wsEmp As Worksheet
Dim rngCheck As Range
Dim c As Range, B As Range, D As Range, F As Range, G As Range
Dim c As Range, H As Range, I As Range, J As Range

Set wsEmp = Sheets("Employee(s)")
Set wsPay = Sheets("Employee(s) Pay")
Set rngCheck = wsEmp.Range("A:A")

If Intersect(Target, [A:A]) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
For Each c In Intersect(Target, [A:A])
Range("B" & c.Row).Formula =
"=LOOKUP(A:A,'Employee(s)'!A:A,'Employee(s)'!B :B)"
Range("D" & c.Row).Formula =
"=LOOKUP(A:A,'Employee(s)'!A:A,'Employee(s)'!G :G)"

:-
Range("F" & c.Row).Formula =
"=IF(E240,(E2-40)*(1.5*D2)+(D2*40),D2*E2)"
Range("G" & c.Row).Formula = "=ROUND(F2*0.154,2)"
Range("H" & c.Row).Formula = "=ROUND(F2*0.0765,2)"
Range("I" & c.Row).Formula = "=ROUND(F2*0.0308,2)"
Range("J" & c.Row).Formula = "=ROUND(F2-G2-H2-I2,2)"
:-

Next c
Application.ScreenUpdating = True

End Sub


Thanks, Any help would be appreciated.


--
cbrd
------------------------------------------------------------------------
cbrd's Profile: http://www.excelforum.com/member.php...o&userid=30009
View this thread: http://www.excelforum.com/showthread...hreadid=497487

Formula problem in code
 

Instead of writing macro, why dont you put formulas straight into your
excel sheet
For eg In the "Hourly rate" column on "Employee(s) pay" sheet you can
write formula
as below =VLOOKUP(A2,'Employee(s)'!A2:G4,7,FALSE)

Then, copy this cell & paste it in A3, A4 tilll the end
So, when you type the employee ID in column A - this formula will
evaluate & lookup & fetch the hourly rate from the next sheet & bring
it back here

If you want to handle #N/A scenario, you can write above formula in
this way
=IF(ISNA(VLOOKUP(A2,'Employee(s)'!A2:G4,7,FALSE)), "NOT FOUND",
VLOOKUP(A2,'Employee(s)'!A2:G4,7,FALSE))

Does this help ?

Kalpesh

Formula problem in code
 

I would suggest that you don't make it a batch process but a singleton
process based upon the number entered, tyhen you wouldn't need to worry
about any missing entries etc. Just add the next number, and the rest fills
in.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rngCheck As Range

On Error GoTo ws_exit:
Application.EnableEvents = False

Set rngCheck = Me.Range("A:A")

If Intersect(Target, rngCheck) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
With Target
Range("B" & .Row).Formula =
"=LOOKUP(A:A,'Employee(s)'!A:A,'Employee(s)'!B :B)"
Range("D" & .Row).Formula =
"=LOOKUP(A:A,'Employee(s)'!A:A,'Employee(s)'!G :G)"
Range("F" & .Row).Formula =
"=IF(E240,(E2-40)*(1.5*D2)+(D2*40),D2*E2)"
Range("G" & .Row).Formula = "=ROUND(F2*0.154,2)"
Range("H" & .Row).Formula = "=ROUND(F2*0.0765,2)"
Range("I" & .Row).Formula = "=ROUND(F2*0.0308,2)"
Range("J" & .Row).Formula = "=ROUND(F2-G2-H2-I2,2)"
End With

ws_exit:
Application.EnableEvents = True
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"cbrd" wrote in message
...

Im just learning this and have almost no clue what Im doing.
I have the following code in my worksheet. It 'seems' to be working
fine except the formulas I have between the Emoticons. Right now I have
the proper functions I would like in there but, D2 should be Column D,
E2 should be Column E, F2 should be Column F, and etc.. Each row is
going to have the same formulas in them columns. Also it would help if
it wouldnt display "#N/A" or "#NAME?" when data is not there. And 1
more thing, If I put a employee ID# that doesnt exist, it still puts
info in.

I put an example .xls here, It will probably give you a better idea:


http://www.ashleylandscaping.com/sample.xls


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim wsPay As Worksheet, wsEmp As Worksheet
Dim rngCheck As Range
Dim c As Range, B As Range, D As Range, F As Range, G As Range
Dim c As Range, H As Range, I As Range, J As Range

Set wsEmp = Sheets("Employee(s)")
Set wsPay = Sheets("Employee(s) Pay")
Set rngCheck = wsEmp.Range("A:A")

If Intersect(Target, [A:A]) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
For Each c In Intersect(Target, [A:A])
Range("B" & c.Row).Formula =
"=LOOKUP(A:A,'Employee(s)'!A:A,'Employee(s)'!B :B)"
Range("D" & c.Row).Formula =
"=LOOKUP(A:A,'Employee(s)'!A:A,'Employee(s)'!G :G)"

:-
Range("F" & c.Row).Formula =
"=IF(E240,(E2-40)*(1.5*D2)+(D2*40),D2*E2)"
Range("G" & c.Row).Formula = "=ROUND(F2*0.154,2)"
Range("H" & c.Row).Formula = "=ROUND(F2*0.0765,2)"
Range("I" & c.Row).Formula = "=ROUND(F2*0.0308,2)"
Range("J" & c.Row).Formula = "=ROUND(F2-G2-H2-I2,2)"
:-

Next c
Application.ScreenUpdating = True

End Sub


Thanks, Any help would be appreciated.


--
cbrd
------------------------------------------------------------------------
cbrd's Profile:
http://www.excelforum.com/member.php...o&userid=30009
View this thread: http://www.excelforum.com/showthread...hreadid=497487

Formula problem in code
 

Well I thankyou for the replies. I was trying to make it so I didnt have
to manually paste the code in each cell/row. When I paste row 2 or
insert it to try and make the functions availiable for line 3, they
still have all the same values from row 1 in there (E2 instead of E3,
G2 instead of G3, and etc.). I was trying to avoid having to manually
enter each function if I end up having 100 lines or so in the
"Employee(s) Pay" worksheet. I will be entering data in here every
week. It will be used for employee pay history throughout the year. I
figured if I could have a macro assign a specific functions to specific
rows, I could avoid having to keep manually entering the functions.

Again, Thanks for your help Bob


--
cbrd
------------------------------------------------------------------------
cbrd's Profile: http://www.excelforum.com/member.php...o&userid=30009
View this thread: http://www.excelforum.com/showthread...hreadid=497487

Formula problem in code
 

Is this what is required (based on Bob's code)?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rngCheck As Range

On Error GoTo ws_exit:
Application.EnableEvents = False

Set rngCheck = Me.Range("A:A")

If Intersect(Target, rngCheck) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
With Target
Range("B" & .Row).Formula =
"=LOOKUP(A:A,'Employee(s)'!A:A,'Employee(s)'!B :B)"
Range("D" & .Row).Formula =
"=LOOKUP(A:A,'Employee(s)'!A:A,'Employee(s)'!G :G)"
Range("F" & .Row).Formula = "=IF(E" & .Row & "40,(E" & .Row &
"-40)*(1.5*D" & .Row & ")+(D" & .Row & "*40),D" & .Row & "*E" & .Row & ")"
Range("G" & .Row).Formula = "=ROUND(F" & .Row & "*0.154,2)"
Range("H" & .Row).Formula = "=ROUND(F" & .Row & "*0.0765,2)"
Range("I" & .Row).Formula = "=ROUND(F" & .Row & "*0.0308,2)"
Range("J" & .Row).Formula = "=ROUND(F" & .Row & "-G" & .Row & "-H" &
..Row & "-I" & .Row & "2,2)"
End With

ws_exit:
Application.EnableEvents = True
End Sub


"cbrd" wrote:


Well I thankyou for the replies. I was trying to make it so I didnt have
to manually paste the code in each cell/row. When I paste row 2 or
insert it to try and make the functions availiable for line 3, they
still have all the same values from row 1 in there (E2 instead of E3,
G2 instead of G3, and etc.). I was trying to avoid having to manually
enter each function if I end up having 100 lines or so in the
"Employee(s) Pay" worksheet. I will be entering data in here every
week. It will be used for employee pay history throughout the year. I
figured if I could have a macro assign a specific functions to specific
rows, I could avoid having to keep manually entering the functions.

Again, Thanks for your help Bob


--
cbrd
------------------------------------------------------------------------
cbrd's Profile: http://www.excelforum.com/member.php...o&userid=30009
View this thread: http://www.excelforum.com/showthread...hreadid=497487

Formula problem in code
 

Yes, that works great with only one problem. If I type an invalid
employee ID # in it just fills in the employee in the last row on the
Employee(s) sheet. Is there a way to prevent it from displaying Info if
the ID # is incorrect?

Also, any reason why excel freezes when i delete a row from
"Employee(s) Pay" sheet?


Thanks for the help!


--
cbrd
------------------------------------------------------------------------
cbrd's Profile: http://www.excelforum.com/member.php...o&userid=30009
View this thread: http://www.excelforum.com/showthread...hreadid=497487

Formula problem in code
 

Hi,
LOOKUP function will return the last value for an ID not found in
table or #N/A if less than minimum value. Consider using VLOOKUP (or MATCH)
instead with an error check

The formula will look something like:

=if(iserror(Vlookup(Myvalue,Mytable,2))," ",Vlookup(Myvalue,Mytable,2))

where MyValue is the ID
MyTable holds the data .... columns A & B ?
2 selects data from 2nd column in MYtable e.g column B

For the LOOKUP with columns A & G, MyTable will be A:G and 2 will be changed
to 8 (column G) .. I think!


Have to rush off now ....

HTH

"cbrd" wrote:


Yes, that works great with only one problem. If I type an invalid
employee ID # in it just fills in the employee in the last row on the
Employee(s) sheet. Is there a way to prevent it from displaying Info if
the ID # is incorrect?

Also, any reason why excel freezes when i delete a row from
"Employee(s) Pay" sheet?


Thanks for the help!


--
cbrd
------------------------------------------------------------------------
cbrd's Profile: http://www.excelforum.com/member.php...o&userid=30009
View this thread: http://www.excelforum.com/showthread...hreadid=497487

Formula problem in code
 

Hi,

Try this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rngCheck As Range

On Error GoTo ws_exit:
Application.EnableEvents = False

Set rngCheck = Me.Range("A:A")

If Intersect(Target, rngCheck) Is Nothing Then
Exit Sub
Application.EnableEvents = True
End If
Application.ScreenUpdating = False
With Target
Range("B" & .Row).Formula = "=IF(iserror(VLOOKUP(" &
Target.Address(False) & ",'Employee(s)'!A:B,2,False))," & _
"""""" & ",VLOOKUP(" & Target.Address(False) &
",'Employee(s)'!A:B,2,False))"
Range("D" & .Row).Formula = "=IF(iserror(VLOOKUP(" &
Target.Address(False) & ",'Employee(s)'!A:G,7,False))," & _
"0" & ",VLOOKUP(" & Target.Address(False) &
",'Employee(s)'!A:G,7,False))"
Range("F" & .Row).Formula = "=IF(E" & .Row & "40,(E" & .Row &
"-40)*(1.5*D" & .Row & ")+(D" & .Row & "*40),D" & .Row & "*E" & .Row & ")"
Range("G" & .Row).Formula = "=ROUND(F" & .Row & "*0.154,2)"
Range("H" & .Row).Formula = "=ROUND(F" & .Row & "*0.0765,2)"
Range("I" & .Row).Formula = "=ROUND(F" & .Row & "*0.0308,2)"
Range("J" & .Row).Formula = "=ROUND(F" & .Row & "-G" & .Row & "-H" &
..Row & "-I" & .Row & "2,2)"
End With

ws_exit:
Application.EnableEvents = True
End Sub

"cbrd" wrote:


Yes, that works great with only one problem. If I type an invalid
employee ID # in it just fills in the employee in the last row on the
Employee(s) sheet. Is there a way to prevent it from displaying Info if
the ID # is incorrect?

Also, any reason why excel freezes when i delete a row from
"Employee(s) Pay" sheet?


Thanks for the help!


--
cbrd
------------------------------------------------------------------------
cbrd's Profile: http://www.excelforum.com/member.php...o&userid=30009
View this thread: http://www.excelforum.com/showthread...hreadid=497487

Formula problem in code
 

Works perfectly ..Thanks!


--
cbrd
------------------------------------------------------------------------
cbrd's Profile: http://www.excelforum.com/member.php...o&userid=30009
View this thread: http://www.excelforum.com/showthread...hreadid=497487