Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code problem | Excel Discussion (Misc queries) | |||
If/then code problem | Excel Programming | |||
Problem with Code --- Please help | Excel Programming | |||
Can anyone see the problem with this code? | Excel Programming | |||
Syntax Problem with formula code | Excel Programming |