Visual Basic Excel VBA RRS feed

  • Pergunta

  • I'm new here and I come to this forum with a slight desperation and 
     I believe someone here will get me 
    I am not a developer or programmer or anything, but 
     I'm curious enough to mess with me 
    this kind of thing. 
    The thing is, I have a giant here in my report 
    company, in Excel, which must take certain 
    information every week. 
    I've recorded a macro that helped me 
    much, and my humble BASIC 
    knowledge, I wrote some code that until now, 
    decreased my work with this report 
    about 3 hours to 30 minutes, or I'm too 
    hahahahaha ... 
    Here comes the second part. 
    I'm needing just one more 
    function in that damn language VBA 
    probably the most complicated of my "project" because it 
    got anything to work so far, but 
    enough of talking and going to business. 

    It is a simple VLOOKUP, I need. 
    I have a spreadsheet, 
    with a header in the first line, then the 
    the formula starts from the second line of this column "X" 
    in that case is the "P" column, line 2, and 
    thus, "P2". 

    The report generally varies the amount of lines, can 
    be that a week has x rows, on the other y rows and so on ... 
    So I wrote a code to count the number of lines, starting from the bottom up 
    until the penultimate line (ignoring the first line 
    is the header), the following code: 

    Dim row As Integer
    line = Plan1.UsedRange.Rows.Count 
    For i = line To 2 Step -1 

    Beauty so far. Just to clarify, all here in the company 
    are using and will use this my spreadsheet 
    magic, and this is based on the VLOOKUP worksheet that we used in 
    last week. 
    To be more precise, we take 
    the sheet this week and do the VLOOKUP manually 
    Information sheet last week. 
    Got it? For this reason, I need a FILE DIALOG BOX as 
    PICKER, to choose the file you want 
    use as a "matrix" for the VLOOKUP sheet (above). 
    Why such a freshness? 
    Because as everyone here will 
    use, each one saves the spreadsheet in place, then 
    just go through the directory where the file is
    clicking on it and "OK", simple so. 
    I need to be exactly this way. 

    The VLOOKUP manual in nature is just that: 

    = VLOOKUP (A2, 'C: \ Documents and 
    Settings \ filipe.fragasse \ Desktop \ backlog \ [BACKLOG 310511 GERAL.xls] Sheet1 '! $ A $ 2: $ O $ 371, 15, FALSE) 

    As I said earlier, it is necessary to pay attention to detail that 
    spreadsheet will always vary the amount of 
    lines, then the part of the formula shows that $ A $ 2: $ O $ 371 
    is necessary to include something like 

    "$ A $ 2: $ O $" & CStr (row), because you must get the
    information from the entire worksheet. 

    I have here a code that a filepicker caught in the net and 
    which returns the full path of the item, but 

    I believe that according to the VLOOKUP function, you 
    necessary to extract the full path and then the name 
    File between "[]". This is just my speculation, it is you 
    Who will say. 

    C: \ Documents and
     Settings \ filipe.fragasse \ Desktop \ backlog \ [BACKLOG 310511 GERAL.xls]

    Formula that returns the file picker path: 

    'Declare a variable as a FileDialog object.
    The FileDialog Dim fd 

    'Create a FileDialog object as a File Picker dialog box. 
    Set fd = Application.FileDialog (msoFileDialogFilePicker) 

    'Declare a variable to contain the path 
    'Of each selected item. Even though the path is a String, 
    'The variable must be a Variant because For Each ... Next 
    'Routines only work with Variants and Objects. 
    As Variant Dim vrtSelectedItem 

    'Use a With ... End With block to reference the FileDialog 
    With fd 

    'Use the Show method to display the File Picker dialog box 
    and return the user's action. 
    'The user pressed the action button. 
    If. Show = -1 Then 

    'Step through each string in the FileDialogSelectedItems 
    For Each vrtSelectedItem In. SelectedItems 

    Msgbox "The path is:" & vrtSelectedItem 

    Next vrtSelectedItem 
    'The user pressed Cancel. 
    End If 
    End With 

    'Set the object variable to Nothing. 
    Set fd = Nothing 

    I believe it is just that. Just one more detail, since this 
    code got me in the loop for each line 
    he read, he asked me again to choose the file. 
    So therefore, he must 
    choose the file once and use it for all the job service. 

    Thank you very much help from anyone who can give me this little hand!

    quarta-feira, 1 de junho de 2011 16:29


Todas as Respostas