Writing a macro in excel using vba

Closed
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,

This is homework, I am so lost. Here is the problem:

1. Write a macro, Q_1, which will ask the user for the division of an employee and then calculate percentage of employees from that division who are earning above $40,000 among all employees? Set up criteria on Queries worksheet, and answer should be on Queries sheet in the cell indicated. Create two macro buttons, one each on Database and Queries sheet. Macro should be self-sufficient including first clearing the criteria range.

here's the database

Emp ID Last Name First Name Department Division Salary Start Date Birth Date Email Address Age Years In Service Birthday today? Top/Low New Salary G or ***
1011 Gorton Hazel Accounting Copier $27,597.85 2/3/2000 11/21/1964 Gorton_Hazel@wcs.com 45.0 9.7 Wait Middle $30,426.63 Gorton
1012 Preston Liza Engineering Printer $43,394.15 1/26/2006 12/2/1984 Preston_Liza@wcs.com 24.9 3.8 Wait Middle $43,394.15 ***
1041 Tercan Robert R and D Printer $28,043.68 4/16/2002 9/21/1965 Tercan_Robert@wcs.com 44.1 7.5 Wait Middle $28,043.68 ***
1054 Smith Howard Art Copier $25,176.06 4/16/2009 8/9/1987 Smith_Howard@wcs.com 22.2 0.5 Wait Middle $27,756.61 ***
1055 Albert Maxine Marketing Copier $26,040.56 4/8/1999 8/20/1967 Albert_Maxine@wcs.com 42.2 10.6 Wait Middle $27,993.60 ***
1075 Kane Sheryl Art Printer $23,239.44 8/7/2006 8/28/1979 Kane_Sheryl@wcs.com 30.2 3.2 Wait Middle $25,621.48 ***
1152 Henders Mark Accounting Printer $26,646.20 1/21/2000 10/23/1971 Henders_Mark@wcs.com 38.0 9.8 Wait Middle $29,377.44 ***
1153 Plant Allen Accounting Printer $28,043.68 1/13/2009 11/3/1963 Plant_Allen@wcs.com 46.0 0.8 Wait Middle $30,918.16 ***
1167 Berwick Sam Marketing Copier $31,913.88 4/18/2001 9/28/1970 Berwick_Sam@wcs.com 39.1 8.5 Wait Middle $31,913.88 ***
1168 Asonte Toni Engineering Fax $63,035.88 4/10/1999 7/8/1970 Asonte_Toni@wcs.com 39.3 10.6 Wait Tops! $67,763.57 ***
1169 Dorfberg Jeremy Engineering Copier $58,002.05 10/13/1998 7/12/1978 Dorfberg_Jeremy@wcs.com 31.3 11.0 Wait Tops! $62,352.20 ***
1290 Cooper Linda Admin. Fax $26,113.86 1/3/2005 10/1/1966 Cooper_Linda@wcs.com 43.1 4.8 Wait Middle $28,790.53 ***
1299 Simpson Sandrae Engineering Copier $34,854.20 12/21/2005 9/16/1985 Simpson_Sandrae@wcs.com 24.1 3.9 Wait Middle $34,854.20 ***
1300 Richards Phillip Accounting Printer $30,451.68 12/13/1993 9/24/1965 Richards_Phillip@wcs.com 44.1 15.9 Wait Middle $33,572.98 ***
1301 Sindole Randy Marketing Fax $27,081.04 8/6/2008 6/25/1961 Sindole_Randy@wcs.com 48.4 1.2 Wait Middle $27,081.04 ***
1310 Smith Ellen Engineering Fax $32,410.85 10/4/1986 11/2/1965 Smith_Ellen@wcs.com 44.0 23.1 Wait Middle $35,732.96 ***
1311 Cane Nate Marketing Printer $43,486.95 9/26/2002 11/13/1954 Cane_Nate@wcs.com 55.0 7.1 Wait Middle $43,486.95 ***
1329 Vuanuo Tuome Engineering Printer $55,410.85 2/22/2009 5/6/1964 Vuanuo_Tuome@wcs.com 45.5 0.7 Wait Tops! $55,410.85 ***
1330 Selznick Anna Accounting Copier $31,539.24 2/14/1999 9/23/1968 Selznick_Anna@wcs.com 41.1 10.7 Wait Middle $34,772.01 ***
1331 Cash Mary Engineering Printer $49,872.15 5/11/1999 5/21/1964 Cash_Mary@wcs.com 45.5 10.5 Wait Tops! $53,612.56 ***
1333 Szcznyck Tadeuz Engineering Copier $59,362.20 4/16/1995 10/7/1965 Szcznyck_Tadeuz@wcs.com 44.1 14.5 Wait Tops! $63,814.36 ***
1334 Kaneko Midori R and D Fax $28,043.68 4/8/1998 10/18/1965 Kaneko_Midori@wcs.com 44.1 11.6 Wait Middle $30,146.96 ***
1426 Lampstone Pete Marketing Fax $64,689.00 9/8/1985 9/23/1958 Lampstone_Pete@wcs.com 51.1 24.2 Wait Tops! $71,319.62 ***
1516 Bell Tom Accounting Printer $28,549.50 3/6/2004 6/26/1963 Bell_Tom@wcs.com 46.4 5.6 Wait Middle $31,475.82 ***
1517 Quan Karen Engineering Printer $75,589.60 2/26/1989 7/7/1963 Quan_Karen@wcs.com 46.3 20.7 Wait Tops! $83,337.53 ***
1529 Kellerman Tommie Engineering Printer $45,146.68 1/28/2007 9/10/1971 Kellerman_Tommie@wcs.com 38.2 2.7 Wait Middle $45,146.68 ***
1530 Stewart Iain Admin. Printer $25,146.68 1/20/1991 1/15/1967 Stewart_Iain@wcs.com 42.8 18.8 Wait Middle $27,724.21 ***
1531 Lempert Alexandra R and D Copier $41,053.48 5/11/1996 1/19/1967 Lempert_Alexandra@wcs.com 42.8 13.5 Wait Middle $44,132.49 ***
1556 Davison Karen Engineering Printer $29,362.20 11/26/2006 9/23/1987 Davison_Karen@wcs.com 22.1 2.9 Wait Middle $29,362.20 ***
1557 Bates Lisa Admin. Copier $27,081.04 8/18/2009 3/22/1990 Bates_Lisa@wcs.com 19.6 0.2 Wait Middle $29,856.85 ***
1558 Sargent Evelyn Marketing Copier $41,987.40 10/16/2005 9/26/1962 Sargent_Evelyn@wcs.com 47.1 4.0 Wait Middle $41,987.40 ***
1657 Wells Rose Accounting Printer $32,626.80 12/6/2002 10/10/1983 Wells_Rose@wcs.com 26.1 6.9 Wait Middle $35,971.05 ***
1674 Boughton Frank Accounting Fax $27,597.85 3/25/2006 1/17/1980 Boughton_Frank@wcs.com 29.8 3.6 Wait Middle $30,426.63 ***
1675 Melendez Jaime Accounting Fax $29,045.24 3/17/1996 1/28/1964 Melendez_Jaime@wcs.com 45.8 13.6 Wait Middle $32,022.38 ***
1675 Miller Janet Marketing Copier $33,301.44 10/26/1998 9/1/1969 Miller_Janet@wcs.com 40.2 11.0 Wait Middle $35,799.05 ***
1676 Wells Jason Admin. Copier $23,212.32 10/18/2001 9/12/1982 Wells_Jason@wcs.com 27.1 8.0 Wait Lows $25,591.58 ***
1677 Levine Eric R and D Fax $37,895.52 11/6/2007 9/16/1989 Levine_Eric@wcs.com 20.1 2.0 Wait Middle $37,895.52 ***
1723 Alstain Isolde Engineering Fax $69,362.20 8/6/1998 9/23/1965 Alstain_Isolde@wcs.com 44.1 11.2 Wait Tops! $74,564.37 ***
1724 Chu Steven Marketing Printer $28,043.68 7/29/1997 5/21/1965 Chu_Steven@wcs.com 44.5 12.3 Wait Middle $30,146.96 ***
1792 Barton Eileen Art Copier $28,859.25 12/24/2007 10/3/1968 Barton_Eileen@wcs.com 41.1 1.8 Wait Middle $31,817.32 ***
1793 Able Aaron Admin. Fax $24,179.50 12/16/2003 10/14/1976 Able_Aaron@wcs.com 33.1 5.9 Wait Middle $26,657.90 ***
1794 Goldberg Malcolm Marketing Fax $57,488.75 2/18/2008 10/18/1986 Goldberg_Malcolm@wcs.com 23.0 1.7 Wait Tops! $57,488.75 Goldberg
1814 Al-Sabah Daoud Engineering Fax $51,303.60 3/4/1992 9/28/1969 Al-Sabah_Daoud@wcs.com 40.1 17.7 Wait Tops! $55,151.37 ***
1816 Lin Michael Engineering Fax $35,480.40 7/8/2002 9/1/1989 Lin_Michael@wcs.com 20.2 7.3 Wait Middle $35,480.40 ***
1931 Mueller Ursula Accounting Copier $36,101.44 6/20/2009 5/28/1986 Mueller_Ursula@wcs.com 23.4 0.4 Wait Middle $39,801.84 ***
1960 Fontaine Jean Accounting Copier $28,043.68 11/13/1992 3/22/1965 Fontaine_Jean@wcs.com 44.6 17.0 Wait Middle $30,918.16 ***
1961 Mueller Kris Admin. Fax $27,081.04 11/5/2006 4/2/1965 Mueller_Kris@wcs.com 44.6 3.0 Wait Middle $29,856.85 ***
1966 Corwick Rob Art Fax $23,239.44 11/17/1991 5/4/1969 Corwick_Rob@wcs.com 40.5 18.0 Wait Middle $25,621.48 ***
1967 Aruda Felice Admin. Copier $23,212.32 11/9/1991 5/15/1969 Aruda_Felice@wcs.com 40.5 18.0 Wait Lows $25,591.58 ***
1968 Martinez Sara Marketing Copier $35,989.20 1/1/1993 5/19/1969 Martinez_Sara@wcs.com 40.5 16.8 Wait Middle $38,688.39 ***
1975 Franklin Larry Accounting Copier $21,887.95 5/7/1997 9/28/1970 Franklin_Larry@wcs.com 39.1 12.5 Wait Lows $24,131.46 ***
1976 Petry Robin Engineering Printer $33,035.88 4/29/2001 10/9/1979 Petry_Robin@wcs.com 30.1 8.5 Wait Middle $33,035.88 ***
1977 Maguire Mollie Art Copier $30,013.62 6/13/2000 9/21/1981 Maguire_Mollie@wcs.com 28.1 9.4 Wait Middle $33,090.02 ***
1978 Silverberg Jay Engineering Fax $51,878.84 1/5/2008 9/26/1987 Silverberg_Jay@wcs.com 22.1 1.8 Wait Tops! $51,878.84 ***

1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
As this is homework it is better you do yourself with the help of this newsgroup.

I suggest you RECORD the taking the steps required and then see the macro (by clicking "edit") . you will be able to make it more general

set up a loop in the salary column