vlookup in vba

[rvkcools]

i have a data in a sheet1 in A1 row till a30 and i want to get the output data in b1 to b30 cells. the range data is F1:g30.

the vlookup has to run automatically from a1 to a30 and executes output from b1 to b30 by using do while loop.

kindly help me

 

[JackDanIce]

Welcome to the board. Try:

Sub Macro_1()

Dim i as Long
Dim rng as Range
 
Application.ScreenUpdating = False

With Sheets("Sheet1")
  For i = 1 to 30
    On Error Resume Next
    Set rng = .Range("B" & i) = .Range("F1:F30").Find(What:=.Range("A" & i), Lookin:=xlValues, Lookat:=xlWhole).offset(,1)
    On Error Goto 0
    If rng is Nothing Then .Range("B" & i) = "Not found"
    Set rng = Nothing
  Next i
End With

Application.ScreenUpdating = True

End Sub

 

[jscranton]

Try this:

Dim col as Int, i as int
Dim rangeData as Range, lookupRange as Range

col = 2
Set rangeData = Range("F1:G30")

For i = 1 to 30

Set lookupRange = Range("A" & i)

found = Application.VLookup(lookupRange.value, rangeData, col, 0)
Range("B" & i).Value = found

Next i

 

[hippiehacker]

Sub rvkcools()
 For Each cell In Range("A1:A30")
  cell.Offset(0, 1).Value = Application.WorksheetFunction.VLookup(cell.Value, Range("F1:G30"), 2, False)
 Next cell
End Sub

 

[Jonmo1]

Welcome to the board..

Why do you need to use VBA for such a basic Excel Formula in the first place?

Why not just put a plain old ordinary Vlookup formula in B1 and fill down to B30
=VLOOKUP(A1,F$1:G$30,FALSE,0)

 

[rvkcools]

Hi guys... thank you so much for your help. Im a beginner. Hope you guys will help me in learning advanced VBA.

for the above mentioned program i tried in this way

Do While ActiveCell.Offset(0, -1).Value <> ""
ActiveCell.Value = WorksheetFunction.VLookup(ActiveCell.Offset(0, -1).Value, Range("j4:k8"), 2, False)
ActiveCell.Offset(1, 0).Select
Loop
End Sub

 

[rvkcools]

i tried doing this prog but the output on b1 says not found. could you please help me?


Sub Macro_1()

Dim i As Long
Dim rng As Range

Application.ScreenUpdating = False

With Sheets("Sheet2")
For i = 1 To 30
On Error Resume Next
Set rng = .Range("B" & i) = .Range("F1:F30").Find(What:=.Range("A" & i), LookIn:=xlValues, Lookat:=xlWhole).Offset(, 1)
On Error GoTo 0
If rng Is Nothing Then .Range("B" & i) = "Not found"
Set rng = Nothing
Next i
End With

Application.ScreenUpdating = True

End Sub

 

[rvkcools]

this prog works fine but if the cell is blank in a29 then it should come as blank in b29 and a30 has a value then how should i write a code?

Sub rvkcools() For Each cell In Range("A1:A30") cell.Offset(0, 1).Value = Application.WorksheetFunction.VLookup(cell.Value, Range("F1:G30"), 2, False) Next cell End Sub

 

[hippiehacker]

Sub rvkcools()
 For Each cell In Range("A1:A30")
 If cell.Value <> "" Then
  cell.Offset(0, 1).Value = Application.WorksheetFunction.VLookup(cell.Value, Range("F1:G30"), 2, False)
 End If
 Next cell
End Sub