VLookUp Help

dsmith616 · May 17th 2016

I can't get this VLookUp formula to apply to my column.

Brief background:
Tab "DATA" has SQL data being pulled in on a refreshed connection.
Tab "Folder" pulls a list of file names from a designated folder; all in Column A.
In the "Folder" tab, Column B, I'd like to add the VLookUp formula, but when I recorded the macro it still will not apply to all the cells only the first cell (B2).

I can find the lastrow, but not sure why the formula isn't apply to every cell. In the For Loop is my feeble attempts at adding the formula in each cell.

Code

Option Explicit






Sub VLookUp()
'




'
Dim lastRow As Long




Worksheets("Folder").Select
lastRow = GetLastRow("A2")
MsgBox ("The last row is " & lastRow)








Dim i As Integer
For i = 2 To lastRow




'    Range("B2").Select
'    Range("B2" & lastRow).FormulaR1C1 = _
'        "=VLOOKUP(Table1[[#This Row],[LoanNumber]],Folder!C[-1],1,FALSE)"
''    Range("B2").Select
''    Selection.AutoFill Destination:=Range("B2:B4")




'Range("B2").FormulaR1C1 = _
'        "=VLOOKUP(Table1[[#This Row],[LoanNumber]],"Folder!C[-1] & lastRow",1,FALSE)"




Next i
 




End Sub
















Public Function GetLastRow(incomingRng As String)
    GetLastRow = Range(incomingRng).End(xlDown).Row
End Function

Display More

holdec03 · May 19th 2016

Re: VLookUp Help

Maybe the use of vba is overkill here...?

Have you tried these?
https://www.pryor.com/blog/cop…as-down-to-fill-a-column/

dsmith616 · May 19th 2016

Re: VLookUp Help

Because I will not be the individual working on said report/issues it needs to be a "living" document and able to update itself without my intervention each day.

After playing around with some code snippets I was able to piece some things together to make it work. However, it does run SLOW! That would be my only qualm at this point. In case anyone is interested; here is my solution below.

Code

Sub VLookUp()






'Refreshes SQL connection based on name








ActiveWorkbook.Connections("JudgmentFigureDataReferred").Refresh












'Clears contents in Sheets "Folder" for both Columns A & B
    
    Worksheets("Folder").Select
    Range("A2:C2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents












'Pulls in name for each file in specified folder location




Dim ctr As Integer
Dim path As Variant
Dim file As Variant
ctr = 1
path = "J:\DefaultManagement\Timeline Management\Foreclosure Bid Approval\JFIGS\ "   ' Path should always contain a '\' at end
file = Dir(path)    ' Retrieving the first entry.
Do Until file = ""   ' Start the loop.
  Worksheets("Folder").Cells(ctr + 1, 1).Value = Left(file, 10)
  ctr = ctr + 1
  file = Dir()   ' Getting next entry.
Loop








'Adds VLookUp Formula to Column B in Sheet "Folder"




Worksheets("Folder").Range("B2").Select




Range("B2").FormulaR1C1 = "=VLOOKUP(DATA!RC[-1],C[-1],1,FALSE)"




Selection.AutoFill Destination:=Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)








'Adds VLookUp Formula to Column C in Sheet "Folder" to pull in Good Through Date




Worksheets("Folder").Range("C2").Select




Range("C2").FormulaR1C1 = "=VLOOKUP(RC[-1],DATA!C[-2]:C[8],11,FALSE)"




Selection.AutoFill Destination:=Range("C2:C" & Range("B" & Rows.Count).End(xlUp).Row)








'Refreshes Pivot Table




Dim pt As PivotTable




Set pt = Worksheets("Pivot").PivotTables("JFIGS")




pt.RefreshTable
























End Sub

Display More

VLookUp Help

Participate now!

Share

Users Viewing This Thread