Formula problem in code

[cbrd]

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(E2>40,(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.

[Kalpesh]

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

[Bob Phillips]

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(E2>40,(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" <[email protected]> wrote in message
news:[email protected]...
>
> 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(E2>40,(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
>

[cbrd]

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

[Toppers]

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

[cbrd]

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!

[Toppers]

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

[Toppers]

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

[cbrd]

Works perfectly ..Thanks!