excel vba nested for each loop

Question

I am working on excel macro and got stuck at one point. Need help to please resolve it.

I have to look for 2 rows in sheet, and for each value in 1 row look for the cell value in 2 row. If the range of values in row 2 equal to some conditional value, then come out of the row 2 check and set the flag as true. For achieving this I used two For Each loop:

 Sub Sendmail ()
    For Each cell in Rows("5").Cells.SpecialCells(xlCellTypeConstant)
        If cells.Value Like "*@*" Then
            Subj = "Fill the Sheet"
            Recipient = cell.Offset(0,-3).Value
            EmailAddr = cell.Offset.Value
            For Each row In Sheet14.Range("O244:AK244").Cells
                If Not row = '8.00" Then
                    found = False
                Else
                    found = True
                End If
            Next row
            If found = False Then
                Msg = "Hi " & Recipient & vbCrLf & vbCrLf
                Msg = Msg & " Please fill the sheet for this week " & vbCrLf & vbCrLf
                Set MItem = Outlook.CreateItem(oIMailItem)
                With MItem  
                    .To = EmailAddr
                    .Subject = Subj
                    .Body = Msg
                    .Save
                End With
            End If
        End If
    Next
End Sub

The found variable used here is defined as Boolean , but I am not able to use it properly, and every time found = false is executing . I want only once the condition is true for row 2, then only the mail should be created .

I would look at the line 'If Not row = '8.00" Then' first of all you start with a single quote and close with a double quote. If this is a number, then quote marks should not be needed, if it is text use double quotes both sides. — ChrisProsser
– ChrisProsser, Commented Aug 6, 2013 at 12:46

Siddharth Rout · Accepted Answer · 2013-08-06 13:11:02Z

2

Few things that I noticed... I have not tested the code but here are my general observations.

A) One glaring piece of code is If cells.Value Like "*@*" Then. Change it to If cell.Value Like "*@*" Then

B) Change xlCellTypeConstant to xlCellTypeConstants

C) EmailAddr = cell.Offset.Value If you want to pick up the same cell's value, you don't need Offset else specify the parameters of Offset

D) @ChrisProsser has already commented on For Each row In Sheet14.Range("O244:AK244").Cells

E) For God's sake (well ignore that... For your sake), Use Option Explicit! I would strongly advise using Option Explicit I would also recommend having a look at this link (SEE POINT 2 in the link).

Topic: To ‘Err’ is Human

Link: http://www.siddharthrout.com/2011/08/01/to-err-is-human/

answered Aug 6, 2013 at 13:11

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MakeCents · Accepted Answer · 2013-08-06 13:10:37Z

0

You are running that For loop;

For Each row In Sheet14.Range("O244:AK244").Cells
    If Not row = '8.00" Then
        found = False
    Else
        found = True
    End If
Next row

for the entire range without doing anything per condition. This would be the same as only checking the last cell in the range, which is probably True, so thats why you think False is executing. Perhaps your if statement should be in this loop as well? Maybe where found = False is?

edited Aug 6, 2013 at 13:10

answered Aug 6, 2013 at 13:03

MakeCents

7641 gold badge5 silver badges15 bronze badges

Collectives™ on Stack Overflow

excel vba nested for each loop

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related