iterating loop and outputting information

[ekeller]

Hello,
I am new to programming and want to iterate a loop and output certain information each time.

I have many rows of data in different spreadsheets that are all different lengths. I need to see if some of the columns in the row of data meet a certain discipline, i.e. one column <-2 or one that is <0 or two that both must be <0.

I was planning on using if then statements to see if the row matched the criteria, but then if it matches the criteria i need to export this information (i.e. which column matched the criteria) to another worksheet (or run the program in a certain worksheet and get the information from the other worksheets and export it into the current one)

but then i need the loop to go onto the next line and repeat the process. until there are no more lines of information. There is a final summary line that I was thinking about doing " loop until strHeader="total" " or something like that.

Sorry if this is vague, thanks for the help!

 

[ravishankar]

Hi
your intentions are clear. But does not help writing codes. Insert a sheet and name it as summary. Paste the following codes in the macro window ( Alt F11)

sub summarize()
b=1
for a = 2 to sheets.count
x = worksheets(a).cells(rows.count,1).end(xlUp).row
for c = 1 to x
if worksheets(a).cells(a,1) < - 2 then
worksheets("Summary").cells(b,1) = worksheets(a).name
worksheets("Summary").cells(b,2) = "Cell A" & a & ":=  " & worksheets(a).cells(a,1)
b= b+1
endif
next c
next a
endsub

run the macro. if col A (of all sheets) has any cells less than -2, it will list them in the summary sheet. Change cells(a,1) < - 2 to try different columns and different operators and different values.

Ravi

 

[ekeller]

more details

Thanks for the reply but I'm still having trouble.

This is what the workbook looks like from which I want to get the information-

Autos and Transportation <---stuff i dont want
Index summary <---- stuff i dont want
more stuff i dont want
Hub Group Inc HUBG X X X X X (I want this stuff if some of the x's meet certain criteria, i.e. column 17 <-2, or column 20 <0 or columns 12 and 13 both <0.

I then want the information HUBG and which column is the reason in meets the qualification exported to a different excel workbook. And then have it keep going for the rest of the rows but not including the stuff i dont want. Is this possible? Thanks so much