For ... Each

[waggy30]

I have this for each loop. I am trying to figure out how perform a function if the function cannot find any values.
This is my Code

For Each c In List
Sheets(Range("MonthCHoice").Value).Select
If c = Range("CourseName1") Then
c.Copy Destination:=Sheets("Lists").Range("Course45")
Sheets(Range("MonthCHoice").Value).Select
c.Offset(0, 2).Copy _
Destination:=Sheets("Lists").Range("Attendees45")
c.Offset(0, -5).Copy _
Destination:=Sheets("Lists").Range("Date45")
Sheets("Lists").Select
Range("Row").Copy _
Destination:=Range("Dummy").End(xlDown).Offset(1, 0)
Sheets(Range("MonthCHoice").Value).Select
Else
Sheets("Lists").Select
Range("Message").Copy _
Destination:=Sheets("Lists").Range("F25")
End If
Next c

I have already tried it with this section/

Else
Sheets("Lists").Select
Range("Message").Copy _
Destination:=Sheets("Lists").Range("F25")

However it inserts the range "message" regardless of whether no values are found.
Does anyone have any ideas?

[Dave Hawley]

Hi Waggy30


What do you have your variable "c" as? I assume the List is a Listbox?

[waggy30]

Sorry, i seemed to have missed off the top bit of my code.

Dim List As Object
Dim c As Object
Set List = Range((Range("MonthCHoice").Value) & "1")
Sheets(Range("MonthCHoice").Value).Select
For Each c In List
Sheets(Range("MonthCHoice").Value).Select
If c = Range("CourseName1") Then
c.Copy Destination:=Sheets("Lists").Range("Course45")
Sheets(Range("MonthCHoice").Value).Select
c.Offset(0, 2).Copy _
Destination:=Sheets("Lists").Range("Attendees45")
c.Offset(0, -5).Copy _
Destination:=Sheets("Lists").Range("Date45")
Sheets("Lists").Select
Range("Row").Copy _
Destination:=Range("Dummy").End(xlDown).Offset(1, 0)
Sheets(Range("MonthCHoice").Value).Select
End If
Next c

This is the whole code

[Dave Hawley]

Hi

You are using some of Excels key words for names which alone could cause problems. "List" is one and "Row" is another. Try this code and let me know what the address is that's in the message box.

Dim rlist As Range
Dim rcell As Range

Set rlist = Range((Range("MonthCHoice").Value) & "1")
MsgBox rlist.Address

Sheets(Range("MonthCHoice").Value).Select
For Each rcell In rlist

If rcell = Range("CourseName1") Then
rcell.Copy Destination:=Sheets("Lists").Range("Course45")

rcell.Offset(0, 2).Copy _
Destination:=Sheets("Lists").Range("Attendees45")

rcell.Offset(0, -5).Copy _
Destination:=Sheets("Lists").Range("Date45")

Range("Row").Copy _
Destination:=Range("Dummy").End(xlDown).Offset(1, 0)
End If

Next rcell

End Sub

[waggy30]

Thanks, i added some extra code and other bits, and followed your suggestion. and everything works perfectly. Thanks