Hello All,

I have one minor issue that I need help with. After much research, I have found some code that I added to my very own coding to turn the "vlookup" formula that results in the range of cells into the actual value. The issue at hand is I now need to manipulate that code to automatically change the formulas in the cells to the values, without needing to drag a range in the excel worksheet. here is my coding. Thanks.

Sub GetValuesFromClosedWorkbook(fPath As String, fName As String, Shname, OutputRge As String, _
    LookupVal As String, TableArryRge As String, LookupCol As Integer, rangeLkup As Integer)

    With ActiveSheet.Range(OutputRge)
        .Formula = "=VLOOKUP(" & LookupVal & ",'" & fPath & "[" & fName & "]" & Shname & _
                    "'!" & TableArryRge & "," & LookupCol & "," & rangeLkup & ")"
    End With
    
    
End Sub
Sub test()

Dim fPath As String
Dim fName As String
Dim sName As String
Dim destCell As String
Dim lookupCell As String
Dim lookupRange As String
Dim lCol As Integer
Dim beginpos As Long
Dim x As Long
Dim RowCount As Long

    RowCount = ActiveSheet.Range("D" & Rows.Count).End(xlUp).row
    beginpos = 2
    fPath = "T:\blah\billing\blah\Sblahaasfs\Test\"
    If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
    lookupRange = "A2:I29"
    lCol = 9
    fName = "Master1.xlsx"
    sName = "Master1"
   
Application.DisplayAlerts = False
   
    For x = beginpos To RowCount
    
        destCell = "F" & x
        lookupCell = "D" & x
                        
         If Range(lookupCell).Value <> "" Then
            GetValuesFromClosedWorkbook fPath, fName, sName, destCell, lookupCell, lookupRange, lCol, False
        End If
    
    Next x
    
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

If Selection.Address = ActiveSheet.Cells.Address Then ActiveSheet.UsedRange

Selection.Formula = Selection.Value

Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

    
End Sub


The bold/underlined portion is the code that is turning the formulas into values but is forcing the user to select/specify the rows in which to do that. I need it done automatically.


THANKS in advance for the help!!!!!!!