For ... Each
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: For ... Each

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    England
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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?


  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Waggy30


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






  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    England
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    New Member
    Join Date
    Mar 2002
    Location
    England
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com