For ... Each

waggy30

New Member
Joined
Mar 2, 2002
Messages
46
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?
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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
 
Upvote 0
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
 
Upvote 0
Thanks, i added some extra code and other bits, and followed your suggestion. and everything works perfectly. Thanks
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top