VBA Macro required: Calculating an average then substracting it from a range of cells

I am preparing a QA/QC assistance program for our analytical chemistry lab. I need to subtract the ‘blank sample’ values from the actual samples. I have one worksheet that contains all of the samples and blank samples (listed by row). The value for the sample = sample – average of the blank values.


This is where it gets complicated: The average of the ‘sample blanks’ that bracket the ‘sample set’ is use to correct for that sample set. [keeping in mind that the samples and sample blanks may be in different rows as the order is not static and raw data could have +1000 samples (rows) and +500 properties (columns)]


i need a code that does something like this:

COLUMN A:

Blank1
Sample1
Sample2
Sample3
Sample4
Blank2
Sample5
Sample6
Sample7
Sample8
Sample9
Sample10
Sample11
Blank12

COLUMN B and so on …:

Numerical values for the property .. filled from B2: to the end (could be B2:PP5000)!

What I want the macro to do (in a new Sheet)

Column A will be the same

Column B and so on …

Blank1 value
Sample1 value – average(Blank1,Blank2)
Sample2 value – average(Blank1,Blank2)
Sample3 value – average(Blank1,Blank2)
Sample4 value – average(Blank1,Blank2)
Blank2 value
Sample5 value – average(Blank2,Blank3)
Sample6 value – average(Blank2,Blank3)
Sample7 value – average(Blank2,Blank3)
Sample8 value – average(Blank2,Blank3)
Sample9 value – average(Blank2,Blank3) Sample10 value – average(Blank2,Blank3) Sample11value – average(Blank2,Blank3)
Blank12 value

Any hints to get me started?
I am somewhat fluent in VBA…

Thank you for your help!
A.

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.