Vlookup
 

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

Vlookup
 

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


--
Regards
Dave Hawley
www.ozgrid.com
"cstang" wrote in message
...
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

Vlookup
 

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
--------------------





cstang;689342 Wrote:

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


--
Paul
------------------------------------------------------------------------
Paul's Profile: 1697
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=192649

http://www.thecodecage.com/forumz