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

[betty]

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

[MaximS]

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]

[betty]

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

[betty]

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

[betty]

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

[Paul_Hossler]

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