VBA Lookup table / Array

[Vacuum Sealed]

Hi All

I would like to implement a value lookup from a range using VBA, not a cell
formula.

Was toying with something like:

for I = 6 to 3000
for J = 6 to 3000

Sheets("MAIN DATA").Range("AG" & i).Value = LOOKUP( 'SUPPORT
DATA'!$D$11:$J$1000,'SUPPORT DATA'!$G$11:$G$1000, 'MAIN DATA'.Range("I" &
J))

Sheets("MAIN DATA").Range("AH" & i).Value = LOOKUP( 'SUPPORT
DATA'!$D$11:$J$1000,'SUPPORT DATA'!$H$11:$H$1000, 'MAIN DATA'.Range("I" &
J))

Next I
Next J

Of course, I am acutely aware the syntax is way wrong, just hoping someone
can rearrange it so it can make sense..

Thanks heaps

Mick.

 

[Ron Rosenfeld]

Hi All

I would like to implement a value lookup from a range using VBA, not a cell
formula.

Was toying with something like:

for I = 6 to 3000
for J = 6 to 3000

Sheets("MAIN DATA").Range("AG" & i).Value = LOOKUP( 'SUPPORT
DATA'!$D$11:$J$1000,'SUPPORT DATA'!$G$11:$G$1000, 'MAIN DATA'.Range("I" &
J))

Sheets("MAIN DATA").Range("AH" & i).Value = LOOKUP( 'SUPPORT
DATA'!$D$11:$J$1000,'SUPPORT DATA'!$H$11:$H$1000, 'MAIN DATA'.Range("I" &
J))

Next I
Next J

Of course, I am acutely aware the syntax is way wrong, just hoping someone
can rearrange it so it can make sense..

Thanks heaps

Mick.

I'm not sure what values you are looking up, but you can just use the lookup member of the worksheetfunction class:

e.g:

YourVariable = worksheetfunction.lookup(lookup_value, lookup_vector, result_vector)

or

YourVariable = worksheetfunction.lookup(lookup_value, array)

lookup_value can be a single variable or cell reference.
array or lookup_vector & result_vector can be range references

 

[James Ravenswood]

Hi All

I would like to implement a value lookup from a range using VBA, not a cell
formula.

Was toying with something like:

for I = 6 to 3000
for J = 6 to 3000

Sheets("MAIN DATA").Range("AG" & i).Value = LOOKUP( 'SUPPORT
DATA'!$D$11:$J$1000,'SUPPORT DATA'!$G$11:$G$1000, 'MAIN DATA'.Range("I" &
J))

Sheets("MAIN DATA").Range("AH" & i).Value = LOOKUP( 'SUPPORT
DATA'!$D$11:$J$1000,'SUPPORT DATA'!$H$11:$H$1000, 'MAIN DATA'.Range("I" &
J))

Next I
Next J

Of course, I am acutely aware the syntax is way wrong, just hoping someone
can rearrange it so it can make sense..

Thanks heaps

Mick.

The syntax is pretty close. Say we have in A1 thru B3:
11 alpha
13 beta
17 gamma

and C6 contains 13, then:
=VLOOKUP(C6,A1:B3,2,FALSE) would give you beta. In VBA, the
equivalent:

Sub VBALookup()
Dim r1 As Range, v As Variant, I As Integer, b As Boolean
Set r1 = Sheets("Sheet1").Range("A1:B3")
v = Sheets("Sheet1").Range("C6").Value
I = 2
b = False
MsgBox Application.WorksheetFunction.VLookup(v, r1, I, b)
End Sub

will also get you beta

 

[Vacuum Sealed]

If only going to school when I was a tacker in high school was this easy, I
probably would have gone more often with such good teachers..

Thank you both Ron & James..

Mick.

 

[Vacuum Sealed]

Gents

I came up with the following after checking out another section of the code.

It works really well and does almost everything I hoped it would do, with
one exception, it does not trigger the Oops when a time is missing from the
lookup...

Sub Operating_Times()

Dim TDLocCode As String
Dim SDLocCode As String
Dim Oops As Integer

Sheets("MAIN DATA").Select

For I = 6 To 30000

tLoad = Sheets("MAIN DATA").Range("D" & I).Value
If tLoad = "" Then
Exit For
End If

found = False

If Sheets("MAIN DATA").Range("AG" & I).Value = "" Then

TDLocCode = Trim(CStr(Sheets("MAIN DATA").Range("G" & I).Value))

For j = 11 To 30000

SDLocCode = Trim(CStr(Sheets("SUPPORT DATA").Range("D" & j).Value))
If SDLocCode = "" Then
Exit For
End If

If SDLocCode = TDLocCode Then
Sheets("MAIN DATA").Range("AG" & I).Value = Sheets("SUPPORT DATA").Range("G"
& j).Value
Sheets("MAIN DATA").Range("AH" & I).Value = Sheets("SUPPORT DATA").Range("H"
& j).Value

found = True

Exit For

End If

Next j

If found = False Then
Oops = MsgBox("Vendor : " & Sheets("MAIN DATA").Range("H" & I).Value & "
Does not appear to have Operating Times entered?", vbOKOnly)
Cancel = True
End If

End If

Next I

End Sub

Appreciate the look over

TIA
Mick