VBA Lookup table / Array

  • Thread starter Thread starter Vacuum Sealed
  • Start date Start date
V

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.
 
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.
Click to expand...

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
 
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.
 
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
 
Back
Top