1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel: Checking values in multiple cells and transferring values to another worksheet

Discussion in 'Business Applications' started by techie_003, Jun 21, 2006.

Thread Status:
Not open for further replies.
Advertisement
  1. techie_003

    techie_003 Thread Starter

    Joined:
    Jun 21, 2006
    Messages:
    15
    This is the code i got so far it will go though and pick the users that have the appropriate status "A" and paid "No". I need it to then copy those users that are appropriate to the Mail Extract sheet. I think i am ment to have a Dim or something in there arn't I. The macro is under the Modual 1 Thingy... :confused: If you can help me please do so PLEASE. The data to be copyed is on the Band Members sheet.

    =======================================================
    Sub MailExtract01()

    !!! I know i need some DIM stuff here too!!!

    'If filtering
    Sheets("Band Members").Select
    Range("A3").Select
    ActiveCell.Offset(0, 5).Range("A1").Select
    For Each Cell In Range("Status")
    If ActiveCell.Text = "A" And Selection.Offset(0, 2).Text = "No" Then

    !!! This is where i need the copy code to go, I think !!!

    End If
    Next Cell
    Sheets("Mail Extract").Select
    Range("A1").Select
    End Sub
    =======================================================

    I need the code to filter though every person and check if the status is “A” and the Paid is “No”. After the filtering I need every person with an “A” under Status and “No” under Paid to be copied to the “Mail Extract” worksheet. I have got all the code to filter though the cells but how do i get this to then copy the appropriate members to the "Mail Extract sheet. There is a code of the file i am working on attached to this post.

    Any help will be great. The is for my Tafe course and i am stuck please HELP!

    Thanks Techie_003 :confused:
     

    Attached Files:

    • Band.xls
      File size:
      126.5 KB
      Views:
      55
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Re: "I think i am ment to have a Dim or something in there" -- technically you're supposed to declare any/all variables, exactly how you'd do that isn't my strong point.

    The copy code does need to go where you think. However, you need something like:

    If Cell = "A" Then
    If Cell.Offset(0,2) = "No" Then
    [I(]do the copy)[/I]
    End If
    End If


    rather than:

    If ActiveCell.Text = "A" And Selection.Offset(0, 2).Text = "No" Then

    However, it's kind of a rule -- in some circles -- that "we" shouldn't just do homework. Maybe you could expand on "The is for my Tafe course".

    rgds,
    bomb
     
  3. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Why loop at all? Just use autofilter..

    Code:
    Sub MailExtract()
        Dim ws As Worksheet, rngTable As Range, rngFilter As Range, LastRow As Long
        Set ws = Worksheets("Band Members")
        Set rngTable = ws.Range("A2:K" & ws.Cells(ws.Rows.Count, 1).End(xlUp).Row)
        ws.AutoFilterMode = False
        rngTable.AutoFilter field:=6, Criteria1:="A"
        rngTable.AutoFilter field:=8, Criteria1:="No"
        Set rngFilter = ws.Range("A3:K" & ws.Cells(ws.Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
        LastRow = Worksheets("Mail Extract").Cells(ws.Rows.Count, 1).End(xlUp).Offset(1).Row
        Intersect(rngFilter, ws.Range("A:C")).Copy Worksheets("Mail Extract").Range("A" & LastRow)
        Intersect(rngFilter, ws.Range("G:G")).Copy Worksheets("Mail Extract").Range("D" & LastRow)
        Application.CutCopyMode = False
        ws.AutoFilterMode = False
    End Sub
     
  4. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Just FYI, a quick Google showed that TAFE is an Australian education branch - apparently both for those still in school and what we would call continuing education here in the USA.
    Lots of web links, but for some reason I could never find out what TAFE stands for...(I assume it is an acronym).
     
  5. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hmm, certainly hope we didn't do anybody's homework for them.. if so, I apologize.

    Also note that error handling is not done on my routine and if there is no viable range for the SpecialCells (i.e. no records match the filtered criteria) then it will error out on that line.
     
  6. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Cheers slurps. (y)
     
  7. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    And if the sheet's protected. :p ( ;) )
     
  8. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    LOL! Yes, yes, all [referenced] sheets will need to be unprotected first. :)
     
  9. techie_003

    techie_003 Thread Starter

    Joined:
    Jun 21, 2006
    Messages:
    15
    Hey all thank you for your input so far. firefytr i can't use autofilter the task is to using code to filter and copy the code i already asked the tafe. I don't want you all to do this for me i just need to be pointed in the right direction like what command i can use to do this. I have been coding macros for about 2 weeks and this is hard as it gets but i can't get this bit. I also asked that tafe if i was allowed to get help off the net and from other and they where like yeah. Can some one explain to me what code i am missing please. Like the Dim thing i know i need some of them but can someone explain what they do and how to write them please.

    Thanks Braett
     
  10. techie_003

    techie_003 Thread Starter

    Joined:
    Jun 21, 2006
    Messages:
    15
    The type of tafe i am doing is all OLI which is Open learning Inst. I don't have a tutor next to me helping me so i have to turn to the net for help from people like yourselfs. I finished school in 2005 and this is my first year out of school and i am doing this Cert 3. IT course i have done Cert 2 in IT so i getting there. I was a techie for my high school i helped run the network of 200 computer with two teachers as i am in Far North Queensland, Australia. All i am asking is for a little help please i am not cheating i am aloud to get help from the net. The closest tutor to me is in Brisbane and that is a day or two full drive and they are to buzy with other students it is pointless tring to get there help please help me out.!!!
     
  11. techie_003

    techie_003 Thread Starter

    Joined:
    Jun 21, 2006
    Messages:
    15
    What is the best time to get on to these forums??? :eek:
     
  12. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    The best time to get a response here is when America is awake, in all honesty. If you need to set up a multiple timezone tracking system, go to http://www.timeanddate.com/worldclock/personal.html

    What you must remember about macros is that they have to be set up to allow for any circumstances that might change. If your macro copies Band Members 'records' to the Mail Extract sheet, that's a start. But if you then run it again, it will simply append (add below) the same records to the ones you just copied. For that reason, the first thing your macro needs to do is clear the 'target area'. You can do this (albeit crudely) with:

    Worksheets("Mail Extract").Range("A3:D65536").ClearContents

    Another thing is your line For Each Cell In Range("Status"), which is a fixed range (='Band Members'!$F$3:$F$38). Again, this doesn't allow for future changes (adding/removing records), so you need to address that. These 2 lines:

    Sheets("Band Members").Select
    Range("A3", Range("A" & Cells.Rows.Count).End(xlUp)).Select


    will go to the BM sheet and select column A from row 3 to the last used cell (whether there are blanks in between or not). NB: use column A rather than F to process things because of the extra info below the records in F.

    So, to process the BM records, you can use a For Each ... Next Loop (re: "i can't use autofilter" -- I suspect you can but let's not get sidetracked). Such a loop might be:

    For Each Cell In Range("A3", Range("A" & Cells.Rows.Count).End(xlUp))
    (do whatever)
    Next Cell


    bearing in mind that the range doesn't have to be physically selected for it to be processed.

    For the 'do whatever' part, you could use a nested If, like:

    If Cell.Offset(0, 5) = "A" Then
    If Cell.Offset(0, 7) = "No" Then
    Cell.Resize(1, 3).Copy Sheets("Mail Extract").Range("A" & x).End(xlUp).Offset(1, 0)
    Sheets("Mail Extract").Range("D" & x).End(xlUp).Offset(1, 0) = Cell.Offset(0, 6).Value
    End If
    End If

    Basically this is ... for each BM record, if the cell 5 across (Status) = A and the cell 7 across (Paid) = No, then:

    (i) copy the first 3 cells of the record -- Cell.Resize(1, 3) -- to column A on Mail Extract

    (ii) set the value in column D on Mail Extract for the row you just copied to the result of the formula in the 7th cell of the BM record (you don't want to copy the actual formula).

    So if you bolt that together, you get:

    x = Cells.Rows.Count
    Worksheets("Mail Extract").Range("A3:D" & x).ClearContents
    Sheets("Band Members").Select

    For Each Cell In Range("A3", Range("A" & x).End(xlUp))

    If Cell.Offset(0, 5) = "A" Then
    If Cell.Offset(0, 7) = "No" Then
    Cell.Resize(1, 3).Copy Sheets("Mail Extract").Range("A" & x).End(xlUp).Offset(1, 0)
    Sheets("Mail Extract").Range("D" & x).End(xlUp).Offset(1, 0) = Cell.Offset(0, 6).Value
    End If
    End If

    Next Cell


    Re: "Like the Dim thing i know i need some of them but can someone explain what they do and how to write them please." -- if you mean that declaring variables is a requirement of the assignment, I'm not qualified (officially or otherwise) to help you there.

    hth,
    bomb
     
  13. techie_003

    techie_003 Thread Starter

    Joined:
    Jun 21, 2006
    Messages:
    15
    I want to say a big thank you to bomb #21 and everyone that helped me thought this problem. THANK YOU ALL i could have never got that alone :D A job well done guys

    Thanks Techie
     
  14. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Let me get this straight, you're going to school, and they say you can't (via code) use autofilter that you have to loop?!?! I certianly hope not, else they need caned - badly. Give them my email address and I'll explain - in baby steps - why loops are not good and autofilter would be preferred and more efficient here.
     
  15. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Techie, I haven't been to Oz since '88 (performed at the Expo in Brisbane).
    Loved the area and the city of Brisbane - go there and have a drink for me, will you?
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/477177

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice