PDA

View Full Version : VBA HELP



albarracim
06-01-2011, 09:51 AM
I'm new here and I come to this forum with a slight desperation and I believe someone here will get me help! 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 happy! 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
object.
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
collection.
For Each vrtSelectedItem In. SelectedItems

Msgbox "The path is:" & vrtSelectedItem


Next vrtSelectedItem
'The user pressed Cancel.
Else
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!

StudsSW
06-24-2011, 08:46 AM
Man, first of all let's speak portuguese here.

Você escreveu muito!!! E não falou nada.
O que você precisa é um vlookup? que compare mais de uma célula?

Fácil.

Public Function NovoMetodoVLOOKUP(ByRef NomeDaPlanilha As String, ByRef ColunaAComparar1 As Integer, ByRef ColunaAComparar2 As Integer, ByRef ColunaDaResposta As Integer, ByRef ValorAComparar1 As Variant, ByRef ValorAComparar2 As Variant) As Variant
Dim Linha As Long
Dim APlanilha As Worksheet
Set APlanilha = Worksheets(NomeDaPlanilha)
For Linha = 2 To APlanilha.Cells(65536, ColunaAComparar1).End(xlUp).Row
If APlanilha.Cells(Linha, ColunaAComparar1).Value = ValorAComparar1 Then
If APlanilha.Cells(Linha, ColunaAComparar2).Value = ValorAComparar2 Then
NovoMetodoVLOOKUP = APlanilha.Cells(Linha, ColunaDaResposta).Value
Exit Function
End If
Next Linha
End Function

Dessa forma você não você coloca na célula, ao invés de =VLOOKUP ... use =NovoMetodoVLOOKUP