We help IT Professionals succeed at work.
Get Started

Macro help

Last Modified: 2012-05-12
Hi I currently have to do the following by hand to 30 spreadsheets. I’m wondering if someone can write a macro to do this for me. Since I have 30 of these, is it possible to put them all in the same folder and run them all at once, or will I have to copy and paste the macro 30 times? Below are the steps I take and also an attached before and after spreadsheet to show what I am talking about.

a.      Center all data
b.      Format date to xx/xx/xxxx
c.      If there is a row with no = 0 and species = ATS – it gets deleted.
d.      Check that dates are consecutive and year = current year.
If there is a non-consecutive date, it’s highlighted in yellow. If there is a year that is not the current year, the cell is highlighted in yellow.
e.      Change 0’s in the grid field to nulls
f.      Check that there are no site numbers that occur less than 5 times.
If they occur less than 5 times they are highlighted in yellow.
g.      Check that there are no 0’s for length and weight
If there are 0’s highlight them in yellow.
h.      Check the length ranges for each species
All but YEP = 5 – 45
YEP 0.5 – 15
If anything falls outside of the range it’s highlighted in yellow.
i.      Check the weight ranges for each species
All but YEP = .1 – 30
YEP = .1 – 2 or 50 to 900
If anything falls outside of the range it’s highlighted in yellow.
j.      Check that for each date there is only one number listed for site and grid.
k.      If there are mixed sites and grids within a date – highlight in yellow that whole chunk of data.
l.      Check scale field
If species = YEP or WAE, scale = spine
If species = COS, BUR, FAT, SMT, scale should be = none
  If species = LAT, sites = between 160 and 197, scale = none
  Everything else, scale = scale
        Anything that falls outside of those rules should be highlighted in yellow.
m.      Count each species by month and site and create a new worksheet to hold those data in table form. I don’t care what order the species are listed in, as long as they are all included. There will be different ones that show up in each spreadsheet. I would like the sites numbers listed in order.
n.      Insert a new column in A = “site” (three digits) & “year” (last two digits only) & “no” (four digits) & “-“ & “species”. Label column “New_biodata_ID”.
o.      Insert a new column in B = to column about without the “-“ & species. Label new column Old_biodata_ID
p.      Insert a third column, labeled “type” insert the word “BIO” for all records in that field.
q.      Insert a new column after weight called R/D.
Watch Question
Social distance; Wear a mask; Get vaccinated
Top Expert 2014
This problem has been solved!
Unlock 1 Answer and 25 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE