0

I would like to use the following code in vba:

Dim negocio As String
Dim feeder As String
Dim origenAWB As String
Dim destinoAWB As String

Set WS = ActiveSheet

negocio = Cells(6, 6).Value
feeder = Cells(8, 6).Value
origenAWB = Cells(10, 6).Value
destinoAWB = Cells(12, 6).Value

For i = 1 To 53
    WS.Cells(24, 1 + i) _
       = Sheets("Proyeccion").Evaluate("=INDEX(6+i,(MATCH(1,(B:B=""" & negocio & """) * (C:C=""" & feeder & """) * (D:D=""" & origenAWB & """) * (E:E=""" & destinoAWB & """),0))")
Next i

For the INDEX part, I need to get the value of the 6+i th column, but I don't know how to do it, since columns are labeled with letters.

What I need to do is to find values that match multiple criteria in a "table". For example, if I have the following table:

   A    B   C   D   E
1  a1  a2  a3  a4  a5
2  b1  b2  b3  b4  b5
3  .   .            .
4  .   .            .
5  .   .            .
. 
.
.

I need the get the value in column E that match the values in columns A, B, C and D. For example, for values a1, a2, a3 and a4 I should get a5. All this, using VBA.

N. Pavon
  • 821
  • 4
  • 15
  • 32

1 Answers1

1

Can you not just make the string dynamic?

As is:
"...=INDEX(6+i,(MATCH(1,..."

New version:
"...=INDEX(" & Col_Letter(6+i)) & ":" & Col_Letter(6+i)) & ",(MATCH(1,..."

Use this to get the letter. It is from this SO post Function to convert column number to letter?

Function Col_Letter(lngCol As Long) As String
Dim vArr
vArr = Split(Cells(1, lngCol).Address(True, False), "$")
Col_Letter = vArr(0)
End Function
Community
  • 1
  • 1
whytheq
  • 34,466
  • 65
  • 172
  • 267
  • do you happen to come up with a faster way to look up values meeting several criteria in a sheet than this method? – N. Pavon Feb 17 '16 at 20:57
  • I've never used evaluate in the way you do, so to-be-honest I do not properly understand what the code is doing. I do know that the fastest structures to use in vba are arrays: can you please add more of an explanation to your original question so I can understand what the code needs to do. – whytheq Feb 17 '16 at 21:26
  • Done, take a look at the post – N. Pavon Feb 18 '16 at 16:35
  • still confused! Say in A2:A4 we have | 1 | 1 | 2 | 3 | then what should show in A5? Or say in B2:B4 we have | 11 | 7 | 11 | 7 | then what should show in B5? – whytheq Feb 18 '16 at 16:40
  • anything, in that table there could be anything... I just want to get the values from that table... the values that match the conditions that I'm looking for... like a VLOOKUP, but with multiple criteria – N. Pavon Feb 18 '16 at 17:31
  • well you could load all the values in the range("A1:D234999") into a variant array in vba; then you could loop through that array testing your conditions...and adding the answers into a second, initially empty, variant array. Once the loop is finished the array is added to the answer sheet. Have a play and then ask another SO question - mention speed and variant arrays and you should get lots of help. Please post a link back here as I'll be interested. – whytheq Feb 18 '16 at 17:48