+ Reply to Thread
Results 1 to 4 of 4

Vlookup in VBA

  1. #1
    Registered User
    Join Date
    08-14-2007
    Posts
    1

    Vlookup in VBA

    How do you writing a VBA function for vlookup?I tried the following but it doesn't work. Can anyone spot the what is wrong?

    Sub fx_vba_test()

    Set startcell = Application.InputBox(prompt:="Please select cell", Type:=8)

    startcell.CurrentRegion.Select

    Dim i As Integer
    For i = 1 To startcell.CurrentRegion.Rows.Count
    startcell.Cells(i, 11).FormulaR1C1 = Right(startcell.Cells(i, 3), 3)
    startcell.Cells(i, 12).FormulaR1C1 = Left(startcell.Cells(i, 3), 3)

    startcell.Cells(i, 14).FormulaR1C1 = Application.WorksheetFunction.vlookup(startcell.Cells(i, 12), Windows("acfx").Worksheets("sheet1").Columns("A:B"), 2, False)

    startcell.Cells(i, 15).FormulaR1C1 = startcell.Cells(i, 13) / startcell.Cells(i, 14)
    startcell.Cells(i, 18).FormulaR1C1 = startcell.Cells(i, 17)
    startcell.Cells(i, 20).FormulaR1C1 = startcell.Cells(i, 18) - startcell.Cells(i, 15)


    Next


    End Sub

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202
    Follow the Forum Rules and wrap code with Code Tags
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Forum Contributor WinteE's Avatar
    Join Date
    04-07-2007
    Location
    Netherlands
    Posts
    544
    Take a look on my site, "Multiple LookUp".
    Just keep it simple !


    http://www.excelguide.eu
    In English as well as in Dutch

  4. #4
    Forum Contributor
    Join Date
    11-17-2006
    Posts
    152
    I do this to look up cells, all it does is seaches for it on a loop. the only down side is you have to know what column the data is in.

    Please Login or Register  to view this content.
    That returns the row it is on so then all you have to do to reference it is

    Please Login or Register  to view this content.
    so to reference column C all you do is this


    Please Login or Register  to view this content.
    Hope this helps

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1