VB code

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: VB code

  1. #1
    New Member
    Join Date
    Mar 2003
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VB code

     
    I have an excel spreadsheet monitoring work done by staff at locations across England. Each row contains a project and is staffed by four people identified with column headings Lead, assist1 assit2 and cord. A staff can be a lead in one project and an assist in another. I need help with a VB code that will be able to pull out all projects undertaken by staff A whether they are the lead or assist1 and display the result information on a black spreadsheet within the work book called analysis. Thanks for you help all VB experts


  2. #2
    MrExcel MVP erik.van.geit's Avatar
    Join Date
    Feb 2003
    Location
    Belgium 3272 Testelt
    Posts
    17,832
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VB code

    an example will make things clearer to help
    display a "before - after - sheet"
    use Colo's HTML Maker utility for displaying your Excel Worksheet on the board (see bottom of the page)

    regards,
    Erik
    I love Jesus

    email Erik

    founder of DRAFT

    my free Addins
    Table-It download & info
    Formula Translator 04

  3. #3
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Redmond, WA
    Posts
    29,498
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VB code

    Welcome to the Board!

    As Eric said, posting a shot of you sheet will certainly help, but you may want to look into Pivot Table(s) as a start as well.

    And what do you mean by "black sheet"? Didja mean "blank"?

    How do your folks access the workbook? Does it need to be shared? (Shared workbooks can be tricky with VBA)...

    Smitty

  4. #4
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,693
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    just another couple of questions:

    are you wanting to copy/paste or cut/paste the info?
    and will the worksheets your working with be changing? or remain the same two sheets throughout?
    Regards,
    Zack Barresse
    My Book on Excel Tables
    My Blog @ ExcelTables.com
    (If you would like comments in any code, please say so.)

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Phoenix, Arizona
    Posts
    1,711
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VB code

    This stumped me this afternoon, but I think niijerry is trying to get some code that will end up with results like this.

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book1.xls___Running: 11.0 : OS = Windows Windows 2000
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    1
    Data
    2
    ProjectLeadAssist1Assist2Cord
    3
    1JoeFredSallyFrank
    4
    2JoeSallyFredFrank
    5
    3FredJoeSallyFrank
    6
    4FrankFredSallyJoe
    7
    5SallyJoeFredFrank
    8
    9
    10
    Results on a seprate tab
    11
    12
    Joe1, 2, 3, 5
    13
    Fred1, 3, 4
    14
    Frank4
    15
    Sally2, 5
    Sheet1

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

  6. #6
    New Member
    Join Date
    Mar 2003
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VB code

    The spreadsheet contains approx. 1500 projects. Each staff member will be working btwn 5- 10 projects at a time. They can either be a lead, assist1 or cord on any of the projects. The sheet is used to monitor their workload and project info changes every month with details of time spent on each project. Each staff should be able to pull out their projects to monitor each project stage. So the idea was to write a VB code to display ony the info required by ecah staff. THis should not only display the project no. as suggested by Travis but every detail contained in that project.

    Thanks for all your help

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Phoenix, Arizona
    Posts
    1,711
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VB code

    What about using filters? This is a standard feature found on the Data menu.

  8. #8
    MrExcel MVP erik.van.geit's Avatar
    Join Date
    Feb 2003
    Location
    Belgium 3272 Testelt
    Posts
    17,832
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VB code

      
    Try something like this
    based on Travis' example for staffnames input
    Code:
    Option Explicit
    
    Sub list_projects()
    Dim i As Long
    Dim max As Long
    Dim staff As String
    Dim sshnr As Integer 'source sheet#
    Dim tshnr As Integer 'target sheet#
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    staff = InputBox("please fill in your name", "NAME", "")
    max = Range("A65536").End(xlUp).Row
    'should be a form with all names
    'isn't casesensitive: users may type without uppercase
    sshnr = ActiveSheet.Index
    Sheets.Add after:=Worksheets(Worksheets.Count)
    tshnr = ActiveSheet.Index
    Sheets(tshnr).Name = staff & " " & Format(Month(Date), "mmmm")
    
    For i = 1 To max
        If Application.WorksheetFunction.CountIf(Sheets(sshnr).Range("B" & i & ":E" & i), staff) > 0 Then
        Sheets(sshnr).Range("B" & i & ":Z" & i).Copy 'range has to be adapted
        Sheets(tshnr).Activate
        Range("A65536").End(xlUp).Offset(1, 0).Activate
        ActiveSheet.Paste
      End If
    Next i
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    End Sub
    I wonder which way there is to avoid jumping from one worksheet to another
    why doesn't this work ?
    Code:
    Sheets(sshnr).Range("B" & i & ":Z" & i).Copy Destination:=Sheets(tshnr).Range("A65536").End(xlUp).Offset(1, 0)
    (this should be quicker if it worked)

    There is some errortrapping to do.

    regards,
    Erik
    I love Jesus

    email Erik

    founder of DRAFT

    my free Addins
    Table-It download & info
    Formula Translator 04

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