Macro Loop to Extract Specific Values and Min/Max Values From Column/Rows Range

**ExcelQuestFL** · 06-03-2009, 11:54 AM

Hi, I'm very new to writing Excel Macro's and wanted to know if I could do the following. Conceptually, I understand what I need done and think it should be fairly straightforward.

There's 2 main events in this loop (I hope that's the correct terminology):

Input 1) User defines the beginning cell to start the loop. In this case, A2.

Input 2) User defines the range of columns/rows to display. The formula for rows that I've thought of is 4r. So if a user wants 20 rows below cells A2, they simply input 5 for r. The number of columns is a constant 5. So if r=5, then I'd want the range to be A2:E22.

Logic)
- Find Value in A2, and Input that value into a new worksheet in cell A2.
- Find Value in B2, and Input that value into a new worskheet in cell B2.
- Find Min(C2:C22), and Input that value into a new worksheet in cell C2.
- Find Max(D2:D22), and Input that value into a new worksheet in cell D2.
- Find Value in E22, and Input that value into a new worksheet in cell E2

Then continue the process except now the beginning Cell Value will be A23, and all extracted values into the new worksheet are input into A3, B3, etc, and this runs to end of values in worksheet 1. That's why I'm thinking this is a loop process.

Hope someone understands and can assist.

**StephenR** · 06-03-2009, 12:03 PM

That's probably do-able, but you should change the title of your thread to be more descriptive of your problem and I would suggest attaching a sample workbook illustrating your request.

**starryknight64** · 06-03-2009, 12:05 PM

In your logic, are you wanting to create 5 new worksheets for every row?

Or just one new worksheet per row?

Or is this all in one new worksheet, just different rows?

**royUK** · 06-03-2009, 12:07 PM

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.

**ExcelQuestFL** · 06-03-2009, 12:08 PM

Thanks for the reply. Here's a sample of the data.

**ExcelQuestFL** · 06-03-2009, 12:10 PM

Originally Posted by starryknight64

In your logic, are you wanting to create 5 new worksheets for every row?

Or just one new worksheet per row?

Or is this all in one new worksheet, just different rows?

All in one new worksheet just different rows.

By the way, will change thread title. Sorry about that.

EDIT - Every new value r will have its own Worksheet rather.

**royUK** · 06-03-2009, 12:10 PM

Edit your title before I close the Thread!

**StephenR** · 06-03-2009, 12:54 PM

See if I've understand your request. This puts the results on sheet2.

Please Login or Register  to view this content.

**ExcelQuestFL** · 06-03-2009, 02:43 PM

A big THANKY YOU for the effort. What we have here is close and I think a couple minor tweaks and should be working.

Sheet 2 should be outputting 5 total columns. Current output is 4.

Let me try to clarify a bit more and let's assume we want data every 20 rows:

- Looking at Worksheet2 Column A: Cell A2 value should be the same as start cell from original sheet. Then the 2nd Value Worksheet 2 cell A3, should be the cell that's 20 rows below the start cell on 1st sheet so startcell+20(or "r"). If we started at sheet 1 cell A2, then Sheet 2 A3 should be cell A22 from sheet 1. The 3rd value, Worksheet2 Cell A3, should now be 1st sheet startcell+40 etc.

- Worksheet2 Column B will use same logic as Column A. In original sheet, the StartCell+1column will give us the cell for Worksheet2, B2. So if Sheet 1 A2 is start cell, then Sheet B2 is value for Sheet 2 B2. Sheet 2 B3 will be Sheet1 B22. The third value for Worksheet2 Cell A3 should now be 1st sheet startcell+1column+40.

- Worksheet2 cell C2, is the maximum value from sheet1 (C2:C21). Worksheet 2 C3 will now be the maximum value from sheet 1 (C22:C41). And so on.

- Worksheet2 cell D2. is the minimum value from sheet1 (D2:D21). Worksheet 2 D3 will now be the minimum value (D22:D41). And so on.

- Worksheet 2 cell E2 is Sheet 1, Start Cell + 5 Columns - 20 rows. Assume start cell A2 Sheet 1 with 20 row input, Sheet 2 E2 should be Sheet 1 cell E21. Sheet 2 E3 should be Sheet 1 cell E41. And so on.

I hope that clarifies a bit and I apologize if the original request wasn't specific enough. If something is unclear, by all means please let me know. Again I appreciate the assistance very much.

**StephenR** · 06-03-2009, 04:34 PM

Thanks for the PM, my pleasure. Try this. I'm still not 100% sure if the interval is 20 or 20+1. If this code doesn't do the job it would be helpful if you could add to your attachment the expected results based on your example.

Please Login or Register  to view this content.

**ExcelQuestFL** · 06-03-2009, 05:25 PM

The first 2 columns looks like we have the right values. The other 3 don't seem to be working correctly. I've attached a sample spreadsheet. This assumes we have rows of 12 beginning in A2. Sheet 2 is the macro values and Sheet 3 are desired values.

**StephenR** · 06-03-2009, 06:12 PM

This works for your example. That has max in C and min in D whereas your original post said the reverse so I have gone with the example.

Please Login or Register  to view this content.

**ExcelQuestFL** · 06-03-2009, 06:29 PM

I'm sorry. You're right. Thank you very much! I'll now study this code so I can learn it.

**StephenR** · 06-03-2009, 06:32 PM

No problem. Once you've had a chance to check, if the code does what you want please could you mark the thread as Solved?

**yagami** · 12-19-2010, 08:38 AM

Hi,

I am trying to write a macro which searches for a empty cell in a Column which is in Date MMDDYYY format and subtract 45 from the value of other column value.

Eg: Searching for blank values in column I and suppose blank cell is found at I1 then do K1-45 and in similar manner if in column K any blank cell is found then do I+45.

Please let me know the marco code for same.

Thanks!

**WasWodge** · 12-19-2010, 09:19 AM

Yagami, you need to post your question in a new thread then you might get some help as it is not related to this thread

Macro Loop to Extract Specific Values and Min/Max Values From Column/Rows Range

LinkBack

Thread Tools

Rate This Thread

Display

Macro Loop to Extract Specific Values and Min/Max Values From Column/Rows Range

Re: Possible to write a Macro to do this?

Re: Possible to write a Macro to do this?

Re: Possible to write a Macro to do this?

Re: Possible to write a Macro to do this?

Re: Possible to write a Macro to do this?

Re: Possible to write a Macro to do this?

Re: Macro Loop to Extract Specific Values and Min/Max Values From Column/Rows Range

Re: Macro Loop to Extract Specific Values and Min/Max Values From Column/Rows Range

Re: Macro Loop to Extract Specific Values and Min/Max Values From Column/Rows Range

Re: Macro Loop to Extract Specific Values and Min/Max Values From Column/Rows Range

Re: Macro Loop to Extract Specific Values and Min/Max Values From Column/Rows Range

Re: Macro Loop to Extract Specific Values and Min/Max Values From Column/Rows Range

Re: Macro Loop to Extract Specific Values and Min/Max Values From Column/Rows Range

Re: Macro Loop to Extract Specific Values and Min/Max Values From Column/Rows Range

Re: Macro Loop to Extract Specific Values and Min/Max Values From Column/Rows Range

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions