-
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
-
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]
-
I'm on it...will let you know how I make out.
-
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
-
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
-
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
-
Forum Rules