Writing a Macro

I'm trying to set up a macro on my payroll sheet that will calculate if FICA 
is due.   Basically, I have a list of employees and I need to be able to 
account for those employees that cap out of FICA.  Right now, I've got a YTD 
column (C), Employees (Rows 1-15), FICA (D).  The amount entered in Column D 
(FICA) depends on how much the employee has earned YTD.  For example, FICA 
will be paid by all employees who have made less than 97000 (Column C).  Once 
an employee reaches 97000, then they no longer pay FICA.  We used to use 
Lotus and I'm not familiar with how a macro works in Excel.  Where would I 
start to create a macro???? 
0
tia (26)
9/5/2007 9:04:16 PM
excel 39879 articles. 2 followers. Follow

3 Replies
266 Views

Similar Articles

[PageSpeed] 0

No need for a macro.  Use a formula like this in cell D2:

=IF(C2<97000,B2,IF(C2-B2>97000,0,(B2-(C2-97000))))*0.062

This assumes that the weekly amount is in cell B2.  6.2% is the FICA rate for 2007.


HTH,
Bernie
MS Excel MVP


"Tia" <Tia@discussions.microsoft.com> wrote in message 
news:654DB2A0-67EF-439E-8FE4-5E4CA5D6F525@microsoft.com...
> I'm trying to set up a macro on my payroll sheet that will calculate if FICA
> is due.   Basically, I have a list of employees and I need to be able to
> account for those employees that cap out of FICA.  Right now, I've got a YTD
> column (C), Employees (Rows 1-15), FICA (D).  The amount entered in Column D
> (FICA) depends on how much the employee has earned YTD.  For example, FICA
> will be paid by all employees who have made less than 97000 (Column C).  Once
> an employee reaches 97000, then they no longer pay FICA.  We used to use
> Lotus and I'm not familiar with how a macro works in Excel.  Where would I
> start to create a macro???? 


0
Bernie
9/6/2007 1:33:49 PM
That appears to work, however, I think I need to tweak it a little.  Is there 
a way to make the formula so it recognizes if a cell is greater than or equal 
to the 97000???  The formula works if the number is less than the 97000 or 
greater than.  However, if it is exactly 97000, the formula still calculates 
FICA.  

Thanks for your help!

"Bernie Deitrick" wrote:

> No need for a macro.  Use a formula like this in cell D2:
> 
> =IF(C2<97000,B2,IF(C2-B2>97000,0,(B2-(C2-97000))))*0.062
> 
> This assumes that the weekly amount is in cell B2.  6.2% is the FICA rate for 2007.
> 
> 
> HTH,
> Bernie
> MS Excel MVP
> 
> 
> "Tia" <Tia@discussions.microsoft.com> wrote in message 
> news:654DB2A0-67EF-439E-8FE4-5E4CA5D6F525@microsoft.com...
> > I'm trying to set up a macro on my payroll sheet that will calculate if FICA
> > is due.   Basically, I have a list of employees and I need to be able to
> > account for those employees that cap out of FICA.  Right now, I've got a YTD
> > column (C), Employees (Rows 1-15), FICA (D).  The amount entered in Column D
> > (FICA) depends on how much the employee has earned YTD.  For example, FICA
> > will be paid by all employees who have made less than 97000 (Column C).  Once
> > an employee reaches 97000, then they no longer pay FICA.  We used to use
> > Lotus and I'm not familiar with how a macro works in Excel.  Where would I
> > start to create a macro???? 
> 
> 
> 
0
tia (26)
9/7/2007 4:56:00 PM
Tia,

=IF(C2<=97000,B2,IF(C2-B2>97000,0,(B2-(C2-97000))))*0.062

But if it is 97000 exactly, you should still pay FICA.....  Note that the 97000 should be the YTD 
column, but to get to that total, you would still need to pay FICA, even if you were over 97000, 
since you owe on the amount up to 97000.

Let's say that you made 10000 per week (just for an easy example).  In each of the first 9 weeks, 
you would pay 620 in FICA, and the 10th week, you would pay on the first 7000, to get to the final 
yearly requirement.

HTH,
Bernie
MS Excel MVP


"Tia" <Tia@discussions.microsoft.com> wrote in message 
news:3E75885F-DA00-48E2-89AA-3D9FEC9A04C0@microsoft.com...
> That appears to work, however, I think I need to tweak it a little.  Is there
> a way to make the formula so it recognizes if a cell is greater than or equal
> to the 97000???  The formula works if the number is less than the 97000 or
> greater than.  However, if it is exactly 97000, the formula still calculates
> FICA.
>
> Thanks for your help!
>
> "Bernie Deitrick" wrote:
>
>> No need for a macro.  Use a formula like this in cell D2:
>>
>> =IF(C2<97000,B2,IF(C2-B2>97000,0,(B2-(C2-97000))))*0.062
>>
>> This assumes that the weekly amount is in cell B2.  6.2% is the FICA rate for 2007.
>>
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "Tia" <Tia@discussions.microsoft.com> wrote in message
>> news:654DB2A0-67EF-439E-8FE4-5E4CA5D6F525@microsoft.com...
>> > I'm trying to set up a macro on my payroll sheet that will calculate if FICA
>> > is due.   Basically, I have a list of employees and I need to be able to
>> > account for those employees that cap out of FICA.  Right now, I've got a YTD
>> > column (C), Employees (Rows 1-15), FICA (D).  The amount entered in Column D
>> > (FICA) depends on how much the employee has earned YTD.  For example, FICA
>> > will be paid by all employees who have made less than 97000 (Column C).  Once
>> > an employee reaches 97000, then they no longer pay FICA.  We used to use
>> > Lotus and I'm not familiar with how a macro works in Excel.  Where would I
>> > start to create a macro????
>>
>>
>> 


0
Bernie
9/7/2007 5:03:35 PM
Reply:

Similar Artilces:

COM Writing Problem: Dead COM?
My code has worked in the past, but now it does not. I can open a COM Port using CreateFile, and the handle is valid. GetCommState is successful. SetCommState is successful. GetCommTimeouts is successful. SetCommTimeouts is successful. Next, I try to write to the COM Port using WriteFile, but it returns FALSE and GetLastError = Error 31: A device attached to the system is not functioning. Is there some way to reset the COM Port? I have tried rebooting as well as enabling/disabling the COM Port, but nothing seems to work! Did I damage the COM Port? FYI: This is on a Pocket PC where the...

writing e-mail messges problem
I have Microsoft Outlook 2002 and have been using it for several months without difficulty. Now, when I go to write a new e-mail message from the icon, when I start to type, I can't see the words--they keep coming up a yellow. Also, the spacing between the typed lines is more than 1.0 even though that is what is indicated on the toolbar. And, my signature information at the bottom is also double spaced. I have tried to select and make sure 1.0 spacing is marked, and it is, but the signature and the typing in the message still come up double spaced. Please help! Hi Kathy, ...

Universal Macros?
I have a file that I export to excel and I need to constantly reformat the cells etc. I recorded a Macro for the formatting but everytime I export it again the macro isnt there. How to I record a macro and make it available to any document in excel? Thanks Put the macro in PERSONAL.XLS. This hidden file gets opened every time you start Excel. Bernard "Andrew" <andrew@fredlewis.com> wrote in message news:OVDuxIxlDHA.2456@TK2MSFTNGP09.phx.gbl... > I have a file that I export to excel and I need to constantly reformat the > cells etc. I recorded a Macro for the formatti...

Is there a simple procedure to set my macros in Excel 2003 to be .
I have tried to create a digital signature as instructed in Help. I created it, but it is not in a trusted zone or something like that. How can I set Excel 2003 so that I am free to create and use macros? Shelley I assume you created the certificate/signature using the SelfCert tool in Office. Also, your Macro Security is set to "medium". With your workbook containing macros open, hit ALT + F11 to get to Visual Basic Editor. Select your workbook/project and Tools>Digital Signature>Choose. Pick the certificate you created and OK your way out. Save the workbook and clo...

Macro problem after copying an Excel File
Hello, Can anyone help please? I have a file 2009xxx.xls with many macros. I now wish to copy and call it 2010xxx.xls but the macro's won't run and advise that it needs debugging. Is there any way to update them without re-recording? I've tried to edit the names but they don't run. Kind regards Peter post the code that causes the debug? do you explicitly use the filename in any of the macros? -- Gary Keramidas Excel 2003 "Peter" <pjr@talktalk.net> wrote in message news:%237EDy8TvKHA.1964@TK2MSFTNGP04.phx.gbl... > Hello,...

import text files in 2007 via macro
I am trying to use the macro tool to import a text file into my database. I believe the function/action used to be called "transfer text" but I can't seem to find in 2007. Any help would be appreciated! Zapper, Click the Show All Actions button in the Show/Hide group on the Macro Design Tools tab. -- Steve Schapel, Microsoft Access MVP ZAPPER wrote: > I am trying to use the macro tool to import a text file into my database. I > believe the function/action used to be called "transfer text" but I can't > seem to find in 2007. Any help would be a...

how do i write a formula and keep in in formula form, so it DOESN.
i have to take a computer test and one of the questions is to write a formula i've previously used but write it so the actual formula is shown on the sheet and when its printed so how do i do i write the formula because everytime i do it it calculates but i need it to stay in formula form with the = sign too? Hi! Precede it with an apostrophie: '=SUMIF(A1:A100,B1) Biff >-----Original Message----- >i have to take a computer test and one of the questions is to write a formula >i've previously used but write it so the actual formula is shown on the sheet >and w...

macro ? #2
Used MS Office 97 since 97. Never tried to create and complicated macros. I found the one below in a public domain spreadsheet. The following error open when the macro runs: Ambiguous name detected: GetData. The second Sub GetData() is higlighted in blue. Any help appreciated. I can upload the ss to my website if need be. dlw Sub Module1() Sub GetData() Sub GetData() Dim QuerySheet As Worksheet Dim DataSheet As Worksheet Dim qurl As String Dim i As Integer Application.ScreenUpdating = False Application.DisplayAlerts = False Application.Calculation = xlCa...

How do you start a Macro?
The thing that I am wondering the most is how to start a macros. Adding on to that question where is the tools menu to start the macros? This version of Office is completely different from the others. What version? If you mean 2007 you can use shortcut Alt + F8, or you can go to the developer tab and click on the macro button and run them from there. To get the developer tab, click the office icon (tope left), click excel options, select personalize in the top left corner and make sure "Show developer tab in the ribbon" is selected -- Regards, Peo Sjoblom Excel 95 - Exc...

Swithces in Macro
Hi, I have 3 macros to run. When user run first macro, it will ask for Yes or No option. If user select “yes” automatically 2 macro should run, If user selects “No” 3 macro should run. Can you please tell me how to write switches between these three macro? Regards, Vishu Write them as functions and return the result. As an example Sub Macro1() Dim ans ans = MsgBox("Macro1", vbYesNo) If ans = vbYes Then If Func2 = vbYes Then Func3 End If End If End Sub Function Func2() Dim ans ans = MsgBox("Func2", vbYesNo) Func2 = an...

Macro to add text to a Message
Hi all, Although I have used VBA in Excel, I have never used it in Outlook. Using Outlook 2007, What I would like to do seems simple enough but I have no clue how to do it. All I want to do is add text to the body of a message. I thought something like this would work but I get the error message "Sub of Function not defined". I know I'm missing an event or something but I'm not sure. Can someone please help? Sub APPRVL_REQ_TEXT() Dim Aprvl_Text As String Aprvl_Text = "Your approval is requested for the following contract renewals. Please a...

Read/write excel and word 2007 documents from another application
Hello, I make a comparative analysis in order to decide which "method" to use to access data in office documents from .net application. Data should be both, read and written in word and excel documents! There is ACE, COM and xml on the table... Does anybody can give me a suggestion, hint or similar to make best list of pros and cons for all three approaches? Any help is appreciated, Regards, mco ...

Macro problem #13
Dear Members Hello, my name is Peter Taylor, from Swan Hill, Australia. Which leads me to my next question. I am trying to create a simple macro that when this macro is selected (or even better automatically done when Excel is loaded) of highlighting a cell (from a range say of A1:A100) that has the current date (6/11/2004). I have a long column of dates eg: 5/11/2004 Friday 6/11/2004 Saturday 7/11/2004 Sunday 8/11/2004 Monday 9/11/2004 Tuesday 10/11/2004 Wednesday 11/11/2004 Thursday 12/11/2004 Friday 13/11/2004 Saturday 14/11/2004 Sunday So as each new day comes around t...

auto sort macro
I am trying to use a macro to sort a table of data on worksheet 3, whenever I update a value on worksheet 1 using Excel 2003 (Copy of macro below). Although the macro picks up the change in data on sheet 1 and runs the sort subroutine, nothing happens. I have discovered that by moving the tables to sheet 1 and repointing the macro to the data that it does work and while this is sufficient, I would like to know why the macro does not work when the tables are on sheet 3. Thank you for any advice anyone can provide Private Sub Worksheet_Change(ByVal Target As Range) If Not ...

Change macro to paste as last sheet
I have a macro that copies the selected sheet and puts it before all of the other sheets. How can I change it to put the copy after all of the other sheets (last sheet in the workbook). ActiveSheet.Copy Before:=Worksheets(1) after:=worksheets.count -- Don Guillett SalesAid Software donaldb@281.com "Chance224" <Chance224@discussions.microsoft.com> wrote in message news:3715EC07-62EF-4A08-9CBE-05AF5D9425CE@microsoft.com... > I have a macro that copies the selected sheet and puts it before all of the > other sheets. How can I change it to put the copy after all of ...

Macros stopped running
I wrote a macro attached to fill out the Application_ItemSend event and that's been running for a while on my machine here at work. Today, it is not. I opened the VBasic editor and put a break point on the event and created a new email and sent it but the break point never actuated. I'm guessing that macros are no longer running but I don't know how to re-enable them or why they'd stop in the first place. Any ideas? ======= Richard Lewis Haggard Never mind. Figured it out. Tools\Macros\Security - a dialog box appears. Changed it from high to low. Macro runs. ===== Richard Le...

Which macro we used in the MessageMap for user defined function
Hi guys, how r u, can you please tell me which macro is to be used in the message map for user defined function, that function ID we declare in the resource.h file, actually we are using ON_COMMAND(USER_DEFINED_FUNC_ID, message handler function) but it is not working .(No any error) please tell me what should i used. I use ON_MESSAGE() with the same parameters and it works fine. The function should be prototyped like: RESULT CMainFrame::OnDisplayDialog(WPARAM wParam, LPARAM lParam); Tom <raghunandan_1081@yahoo.com> wrote in message news:1165583596.594505.307210@l12g2000cwl.google...

Need help with simple macros for double checking manual plugs
Hi, I have to do really basic and boring spreadsheet work, and my boss has something against VLOOKUP, he doesn't want people in the office using it. And he is right, because with these spreadsheet the lookup values are of mixed type, with dashes, the spreadsheets have lots of formatting, and there have been many problems. I would like to write a macro for the following pseudo-code COPY ITEM OPEN FINDER PASTE INTO FINDER SWITCH SHEETS SEARCH FOR DATA Then it stops so I can check that it has found the right lookup value, it waits for me to press a key to confirm that I ...

Macro Quick Key
Is there a way to assign a macro quick-key to a macro in my personal.xla (XL 2007) file? In other words, if I would hold down Ctrl-F7 for example, then a particular macro in the .xla file would be executed. Thanks. On Nov 20, 10:13=A0am, Mike H. <Mi...@discussions.microsoft.com> wrote: > Is there a way to assign a macro quick-key to a macro in my personal.xla = (XL > 2007) file? =A0In other words, if I would hold down Ctrl-F7 for example, = then a > particular macro in the .xla file would be executed. =A0Thanks. Mike, I don't think I posted this correc...

Macro to extract clip-art file?
It's been a long time since I wrote my last VBA macro, and I don't want to duplicate one that might already exist. So before I get stuck into it, does anyone have a macro that will do the following please: - Start with a clip art picture selected (or inserted into a worksheet if that's easier). - Finish with its file (WMF or GIF) copied into an Explorer/My Computer folder please? -- Terry, West Sussex, UK ...

macro's after transition office xp to office 2003
I recently changed from office xp to office 2003 I have several workbook linked to each other an one of them contains a few macro's that I - with much effort - concocted in VBA. After the transition I always get an alarm about these macro's, and I can only get them to work by lowering the security-level. How can I make them work without the alarm, and - of course - without lowering the security-level ? Thanks in advance for an advice. Gilbert -------------------------------------------------------------------------------- Mijn Postvak In wordt beschermd door SPAMfighter 3302 spa...

Solver using macros?
Hi, I recorded a macro in which I invoked the Solver (it's an Add-in). When I run the macro the Solver displays a small window which asks whether one wants to revert back to the old parameters or stick with the new solution. Is there anyway I can prevent that window from popping up and just keep the new solutions that Solver produces? The following links should be helpful http://support.microsoft.com/support/excel/content/solver/solver.asp http://www.solver.com/suppstdsolver.htm Jerry Nikhiel wrote: > Hi, > > I recorded a macro in which I invoked the Solver (it'...

Macros / Automate a task
Publisher 2002 Can I record a macros in P2002 - there appears to be the facility to write one, but that's way beyond me. I want to automate reducing the size of a picture to a certain size every time and it's currently a 5 or 6 step process. If this doesn't work in P2002, does it work in P2003 ? Any help appreciated Ains I don't have a "for sure" answer for you...I'm not much of a "macro type"<G> But as I understand it, Publisher does *not* have a macro *recorder* and you'd need to write the thing.(yes...I know. I'ts on my long "...

Extracting macros from a worksheet
Does anyone know if there is a way to extract macros from an excel worksheet that cannot be opened due to a memory reference error? The exact error is as follows. ____________________________________ The instruction at "0x3005c673" referenced memory at "0x00000018". The memory could not be "written". Click on OK to terminate the program Click on CANCEL to debug the program ---------------------------------------------- Even though I get this error message, I can still see the worksheet behind the error, but the error takes priority and then closes Excel. This...

Retrieve and Write data to Great Plains from third party web application
Hello, We are using MS CRM 3.0 and MS Great Plains, plus a third part .Net web application that I am customizing. My third party web app needs retrieve and write data to Great Plains. I read up a little on this and found a couple ways to go about this. I saw stuff on the Integration Manager and eConnect. Which would you suggest to use? Right now, I've been playing around with eConnect. But, I'm having troubles on actually getting specific data back from Great Plains. And, I haven't tried to write anything yet. Do you know of any good manuals or tutorials I could look throug...