Formula Showing, Need the Value Instead

[TrivialT]

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!!!!!!!

[ragulduy]

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 & ")"
        .value = .value
    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
  
    
End Sub

[TrivialT]

yudlugar,

From what I gather by your reply, I just need to take that portion out completely? And then the values will post correctly?

[ragulduy]

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 & ")"
        .value = .value      
end with 
    
End Sub

I added that bit

[TrivialT]

yudlugar!!!!! YOU DA MAN!!!!!!!

THANK YOU SO MUCH! The funny thing is I was playing around with that exact portion you added earlier, I guess I was putting it in the wrong spot.

Thanks so much! Life saver!

[ragulduy]

You might get away with:

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)
        .value = EVALUATE("=VLOOKUP(" & LookupVal & ",'" & fPath & "[" & fName & "]" & Shname & _
                    "'!" & TableArryRge & "," & LookupCol & "," & rangeLkup & ")")
    end with 
    
End Sub

Not sure if that would work though

[TrivialT]

Your first bit worked exactly how I needed it to. Thanks yudlugar!