betty
12-22-2008, 10:00 AM
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
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