Vlookup

[cstang]

Pls help me to change to a proper code.. it looks ugly.
sub Test ()
Dim a As Integer
a = 11
Do Until Cells(a, 2) = Empty
Cells(a, 3) = Application.VLookup(Cells(a, 2),
Sheets("RuWet").Range("IndonWet"), 2, 0)
Cells(a, 4) = Application.VLookup(Cells(a, 2),
Sheets("RuWet").Range("IndonWet"), 3, 0)
Cells(a, 5) = Application.VLookup(Cells(a, 2),
Sheets("RuWet").Range("IndonWet"), 4, 0)
Cells(a, 6) = Application.VLookup(Cells(a, 2),
Sheets("RuWet").Range("IndonWet"), 5, 0)
Cells(a, 7) = Application.VLookup(Cells(a, 2),
Sheets("RuWet").Range("IndonWet"), 6, 0)
Cells(a, 8) = Application.VLookup(Cells(a, 2),
Sheets("RuWet").Range("IndonWet"), 7, 0)
Cells(a, 9) = Application.VLookup(Cells(a, 2),
Sheets("RuWet").Range("IndonWet"), 8, 0)
Cells(a, 10) = Application.VLookup(Cells(a, 2),
Sheets("RuWet").Range("IndonWet"), 9, 0)
a = a + 1
Loop
end sub

Thank you
Cstang

 

[ozgrid.com]

Something like;

Sub Test()
Dim lRow As Long, lStop As Long
Dim rTable As Range

lStop = Cells(Rows.Count, "B").End(xlUp).Row
Set rTable = Sheets("RuWet").Range("IndonWet")

With Application
For lRow = 11 To lStop
Cells(lRow, 3) = .VLookup(Cells(lRow, 2), rTable, 2, 0)
Cells(lRow, 4) = .VLookup(Cells(lRow, 2), rTable, 3, 0)
Cells(lRow, 5) = .VLookup(Cells(lRow, 2), rTable, 4, 0)
Cells(lRow, 6) = .VLookup(Cells(lRow, 2), rTable, 5, 0)
Cells(lRow, 7) = .VLookup(Cells(lRow, 2), rTable, 6, 0)
Cells(lRow, 8) = .VLookup(Cells(lRow, 2), rTable, 7, 0)
Cells(lRow, 9) = .VLookup(Cells(lRow, 2), rTable, 8, 0)
Cells(lRow, 10) = .VLookup(Cells(lRow, 2), rTable, 9, 0)
Next lRow
End With
End Sub

 

[Paul]

Perhaps this will work..



Code:
--------------------


Sub Test()
Dim i As Long, lr As Long, rng As Range
Application.ScreenUpdating = False
lr = Range("B" & Rows.Count).End(xlUp).Row
Set rng = Sheets("RuWet").Range("IndonWet")
For i = 11 To lr
For j = 3 To 10
Cells(i, j).Value = Application.VLookup( _
Cells(i, 2), rng, j - 1, 0)
Next j
Next i
Application.ScreenUpdating = True
End Sub
--------------------





Pls help me to change to a proper code.. it looks ugly.