Do Until condition met

[*shudder*]

Suffering from a hang over and having a mental block.

I want to loop through column D until the word 'computer' is found, apply some code (I have this bit) then exit the loop.

Thanks in advance

 

[VoG]

Why not use the Find method

Dim Found As Range
Set Found = Columns("D").Find(what:="computer", LookIn:=xlValues, lookat:=xlWhole)

 

[*shudder*]

Thanks Peter.

How would I then use that to apply additional code?

I am using this in an email macro and dependant on the cell content in column D I need to add an attachment relating to that product.

Cheers

 

[VoG]

Ah, maybe you are looking for a cell that contains the word computer with other text. In which case

Dim Found As Range
Set Found = Columns("D").Find(what:="computer", LookIn:=xlValues, lookat:=xlPart)
If Found.Value = "computer shopper" Then

 

[*shudder*]

Thanks again Peter that give me what I want, although I do not need the 'xlpart' element as the cells only contain the whole search subject.

Is there any way of ignoring case?

 

[VoG]

I think it should be case-insensitive by default. Just to be sure

Dim Found As Range
Set Found = Columns("D").Find(what:="computer", LookIn:=xlValues, lookat:=xlPart, MatchCase:=False)
If Found.Value = "computer shopper" Then

 

[*shudder*]

Thanks again, I did test case sensitivity prior to posting and your suggestion has corrected it.

My problem now is that when I incorporate this into the rest of my code it adds the attachment even if the word 'computer' is not present. This is an extract of my routine:

With OutMail
    .To = mailAddress
    .Subject = mailSubject
    .Attachments.Add NewWB.FullName
    Set Found = NewWB.Sheet1.Columns("D").Find(what:="Computer", LookIn:=xlValues, MatchCase:=False)
    If Found.Value = "Computer" Then
        .Attachments.Add ("XXX")
    End If
    .HTMLBody = "<font face='Arial' style='font-size:10pt'>" & mailSalutation & _
                "," & "<br><br>" & strbody & "<br>" & Signature
    If Range("A2").Value <> "" Then .Display
        Application.Wait (Now + TimeValue("0:00:02"))
        Application.SendKeys "%s"
End With

 

[VoG]

I'm surprised that doesn't crash if the word isn't found.

Try like this

With OutMail
    .To = mailAddress
    .Subject = mailSubject
    .Attachments.Add NewWB.FullName
    Set Found = NewWB.Sheet1.Columns("D").Find(what:="Computer", LookIn:=xlValues, MatchCase:=False)
    If Found Is Nothing Then Exit Sub
    If Found.Value = "Computer" Then
        .Attachments.Add ("XXX")
    End If
    .HTMLBody = "" & mailSalutation & _
                "," & "" & strbody & "" & Signature
    If Range("A2").Value <> "" Then .Display
        Application.Wait (Now + TimeValue("0:00:02"))
        Application.SendKeys "%s"
End With

 

[*shudder*]

Sorry Peter, I cannot use that as I am sending large lists of data, its only when the 'computer' value is there that I need to add the attachment, if it is not I still need the email to send.

 

[VoG]

Try

With OutMail
    .To = mailAddress
    .Subject = mailSubject
    .Attachments.Add NewWB.FullName
    Set Found = NewWB.Sheet1.Columns("D").Find(what:="Computer", LookIn:=xlValues, MatchCase:=False)
    If Not Found Is Nothing Then
        If Found.Value = "Computer" Then
            .Attachments.Add ("XXX")
        End If
    End If
    .HTMLBody = "" & mailSalutation & _
                "," & "" & strbody & "" & Signature
    If Range("A2").Value <> "" Then .Display
        Application.Wait (Now + TimeValue("0:00:02"))
        Application.SendKeys "%s"
End With