Consulting

Results 1 to 6 of 6

Thread: Solved: Vlookup, have tried and tried, feel like an idiot.

  1. #1

    Solved: Vlookup, have tried and tried, feel like an idiot.

    Hoping someone can help. I?m an old programmer, new to VBA ? don?t expect anyone to write my code, just point out my ignorance.
    I'm using excel 2007, my working spreadsheet is a macro enabled template, and the lookup spreadsheet is a CSV...I put both in attachement with the lookup on tab 2.
    Trying to do a vlookup for each cell in a column. The lookup table is separate from the spreadsheet I am working in. Macro to be run by user (via button). I started by recording a vlookup and working with the code. That I put in a module, but the poor user has to ?pull down the formula into the rest of the column . Here is that code


    Sub GFSubType()
    '
    ' GFSubType Macro
    '

    '
    Range("E2").Select
    ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(RC[5],GiftFundTable.CSV!R1C1:R250C10,7,FALSE)"
    Range("E3").Select
    ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(RC[5],GiftFundTable.CSV!R1C1:R250C10,7,FALSE)"
    Range("E3").Select
    Selection.AutoFill Destination:=Range("E3:E6"), Type:=xlFillDefault
    Range("E3:E6").Select
    Range("E4:E6").Select
    Selection.ClearContents
    Range("E3").Select




    End Sub

    It?s ugly and not user friendly. I've tried if statements, for statements....open to any code. Arrrghhh. So What I am trying to accomplish is for each ?Fund ID? in Row J of my working spreadsheet (that this vb code is in), go out to the other spreadsheet and grab the corresponding Value in Column 7, and put in Column E. I have attached a few records from my spreadsheet... Error I get is 1004

    on the lookup line of code.

    Sub lkuploop()
    Dim lastRow As Integer
    Dim mycell As Range


    lastRow = Range("A65536").End(xlUp).Row

    Set Plotrange = Worksheets("Employee Drive Pledges").Range("E2:E" & lastRow)

    For Each mycell In Plotrange
    Findstring = Cells(mycell.Row, 10)
    mycell.Formula = "=VLookup(Findstring,('GiftFundTable.CSV!$A$1:$G$250'),7,False)"

    Next mycell
    End Sub

    Thanks for your help. Hope I can return the favor some day when I get to where I need to be.

    Betty

  2. #2
    VBAX Mentor MaximS's Avatar
    Joined
    Sep 2008
    Location
    Stoke-On-Trent
    Posts
    360
    Location
    the file is corrupt (at least for me).

    you don't have too use FormulaR1C1 to do the job. In my opinion you my find easier using just .Formula i.e.

    [VBA]Range("B2").Formula = "=VLOOKUP(A2, $C$2:$D$50, 2, 0)" [/VBA]

    and so one. then instead of using autofill and select try to use:

    [VBA]
    Range("B2").Copy Destination:=Range("B3:B" & LastRow)
    [/VBA]

  3. #3
    I'm on it...will let you know how I make out.

  4. #4

    getting closer

    Love the simplicity of formula and copy....

    I get the #Name error - but at least it is on every cell!!
    I think it has something to do with the "table" (2nd argument). Tried naming the range of the Spreadsheet - same result....here is what I have that is resulting in #NAME being returned. If you have any ideas, I am all ears

    Sub lkp()

    Dim lastRow As Integer
    lastRow = Range("A65536").End(xlUp).Row
    Range("E2").Formula = "=VLOOKUP(J2,GiftFundTable.CSV,7,false)"
    Range("E2").Copy Destination:=Range("E3:E" & lastRow)
    End Sub

  5. #5
    Hold the phone.....added workbook, then sheet, then range and voila
    "=VLOOKUP(J2,[GiftFundTable.CSV]GiftFundTable!$A$1:$I$250,7,false)"
    MaximS - thank you and may you get as good a gift for the holidays. Sincere thanks.

    Betty

  6. #6
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,873
    Location
    You had saved your WB as a 2007 formated Macro Template (.XLTM), but with the .XLS extension. I think you meant to save it as a Macro enabled WB (.XLSM). Couldn't open it until I changed the extension (no biggie)

    One thing to consider to to just plug in the values insead of messing around with formulas by using Application.WorksheetFunction.VLookup ()

    My crude macro needs error checking and some things are hard coded, but it's just a proof of concept. I faked another "Looked Up In" workbook, and that's also in the Zip


    [vba]
    Sub Macro1()
    Dim wbMe As Workbook, wbOther As Workbook
    Dim rData As Range, rCell As Range


    Application.ScreenUpdating = False

    Set wbMe = ThisWorkbook


    Set rData = ActiveSheet.Cells(1, 1).CurrentRegion.Columns(10)

    With rData
    Set rData = .Cells(2, 1).Resize(.Rows.Count - 1, 1)
    End With


    Workbooks.Open Filename:="c:\OtherWB.xlsx"
    Set wbOther = ActiveWorkbook
    For Each rCell In rData.Cells
    With rCell
    .Offset(0, -5).Value = Application.WorksheetFunction.VLookup(.Value, wbOther.Worksheets("Codes").Range("A:B"), 2, False)
    End With
    Next
    wbOther.Close
    Application.ScreenUpdating = True
    End Sub
    [/vba]

    Paul

Posting Permissions

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