For Loop to check two cells in each row...

[tekmann33]

I am stuck since I am somewhat of a noob when it comes to VBA. Trying to advance beyond the noob.

Fairly simple problem that I have half figured out.

Basically, I need to look at two columns of numbers in a data sheet. If one or both of those columns in each row is greater than 500, then add that row to another sheet.

My pseudo code would look like:

Get last row.

For loop starting with first row till end of data (last row)
If row i cell C > 500 add entire row to sheet2

Else if row i cell J > 500 add entire roww to sheet 2

End if
Go to next row


I have something that will work with one column, but I need to check the second column if the first doesn't meet the criteria. If someone can point me in the right direction, I would much appreciate it. Ultimately I need to check 5 cells for each row...but if I can see how it is done for two...checking 5 should be easy.

Please Login or Register  to view this content.

[Trebor76]

Hi tekmann33,

Try this from the tab containing the data:

Please Login or Register  to view this content.

HTH

Robert

[tekmann33]

Perfect!

One question:
What exactly is happening with the Else statement in the for loop?

Is it looking for existing data, going to the end of that data and appending to that data?

I will look it up...this is how I learn..but if I wanted to create a new sheet name and copy my results to it...what object method should I be looking at?

[Trebor76]

What exactly is happening with the Else statement in the for loop?

Depends on which statement you mean as I nested two statements. I assume it's the second which is simply finding which row in Sheet2 to use for the data being transfered.

I will look it up...this is how I learn..but if I wanted to create a new sheet name and copy my results to it...what object method should I be looking at?

You can use something like the wstMySheet variable below:

Please Login or Register  to view this content.

If you could mark this thread as solved it would be appreciated.

Regards,

Robert