Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If (Vlookup 0) working, but what if Vlookup cell does not exist | Excel Worksheet Functions | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
using a vlookup to enter text into rows beneath the vlookup cell | Excel Programming | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Which is faster: VLOOKUP-worksheet or VB-array VLOOKUP? | Excel Programming |