Help understanding Loops

[nakia]

Hi,

I have the following requirement i need help with.

I have this below statment that I need to repeat for a cell range I want to define as CellR1 (which will be Worksheet "Automation Range. B10:B20)
but CellR2 also needs to increase to the next cell in its range everytime in repeats (which will be Worksheet "Automation Range. B30:B40)

Please Login or Register  to view this content.

exmaple, first run would be

Please Login or Register  to view this content.

second would be

Please Login or Register  to view this content.

and so on.

I know there are mistakes in the above example..

I am completely new to VBA so I do apoloagize.


To explain further.
what I need this to do is determine if Cell 1 (From Range 1) and Cell 1 (From Range 2) contain specific string, if so run rest of macro
then this needs to repeat for each cell defined in Range 1

Here is my code right now which is only looking at the first combinations of cells. removed some stuff for privacy.

Please Login or Register  to view this content.

[protonLeah]

You should have posted a sample workbook. I think you need something like this:

Please Login or Register  to view this content.

[nakia]

Happy to attach entire sheet.

Looking at the code you provided its not telling how to incrament using 2 different ranges (one for CellR1 and the second range for CellR2)

[protonLeah]

CellR1 in the For Each loop selects all the cells in range B10:B20.

The CellR1.Offset(20, 0). ... looks at the cell 20 rows below the current CellR1 (and in the same column, as specified by the ",0") at the same time. Thus the offset selects B30:B40.

[nakia]

Ended up with this but no Joy.

Please Login or Register  to view this content.

[protonLeah]

What did it not do? Your If statement is looking at B5 & B19 at the same time (then B6 & B20 ...) are those the right pairs? What's actually in those cells? Are they single word text strings "TRUE" and "NO"?

[nakia]

have attched a cropped down version of the sheets.
Basically on sheet Automation if Condition 1 HIGH is TRUE and Condition 1 Sent Mail Log for High is NO then send email.
repeat this for range B5:B14

Hope this makes sense?

[nakia]

What did it not do? Your If statement is looking at B5 & B19 at the same time (then B6 & B20 ...) are those the right pairs? What's actually in those cells? Are they single word text strings "TRUE" and "NO"?

Both of the Cells B5 and B19 Contain the strings the correct strings "TRUE" and "NO" but the email didnt send.
If I remove this IF statment and run the Macro the email sends as expected.

Thanks again

[Kyle123]

Like this:

Please Login or Register  to view this content.

[nakia]

Thanks, that worked.

One last question, if the statment is true (both conditions are met) how do i then change the value of the Offset Cell for that cycle of the Loop.
want it to say " YES" so next time the macro runs it knows not to send email as its already been sent?

[Kyle123]

Put this after the opening IF statement:

Please Login or Register  to view this content.