How to call a file that is currently open for Vlookup?

[mwalls33]

Hi all - I recorded a macro to open a file and find the latest file. I want to know since the date here changes daily, how do I call it from my vlookup?

Sub Open_Argus()
'
' Open Latest Argus File

'---Opens a sheet based on date, searches backward from today til it finds a matching date
Dim dtTestDate As Date
Dim sStartWB As String

Const sPath As String = "V:\Security\ARGUS\Argus Users Lists\2018"
dtEarliest = Date - 5
'--to stop loop if file not found by earliest valid date.
dtTestDate = Date
sStartWB = ActiveWorkbook.Name

While ActiveWorkbook.Name = sStartWB And dtTestDate >= dtEarliest
On Error Resume Next
Workbooks.Open sPath & "IPNS Security " & Format(dtTestDate, "MMDDYYYY") & ".xlsx"
dtTestDate = dtTestDate - 1
On Error GoTo 0
Wend

If ActiveWorkbook.Name = sStartWB Then MsgBox "Earlier file not found."

end sub

heres my formula for vlookup, the date here should be based from the result above.

Dim LR As Long

LR = Range("E" & Rows.Count).End(xlUp).Row
Range("Q2").Select
Range("Q2:Q" & LR).FormulaR1C1 = _
"=IF(VLOOKUP(RC[-8],'[IPNS Security 07232018.xlsx]Sheet1'!C8,1,FALSE),""ARGUS"")"

 

[offthelip]

Since you now have the latest file open as the active workbook , why don't you write the lookup without the file name: e.g.

"=IF(VLOOKUP(RC[-8],'Sheet1'!C8,1,FALSE),""ARGUS"")"

 

[mwalls33]

Technically I have 3 workbook. One that houses all the macros and where I need open so I can call all of my macros from any workbook open. Another is the workbook I'm working on to find the value on another workbook. So I need to write the macro to the macro workbook so it can be use to all other file when needed?

 

[offthelip]

I think you only need to make a small change as:

Workbooks.Open sPath & "IPNS Security " & Format(dtTestDate, "MMDDYYYY") & ".xlsx"
[COLOR=#ff0000]tst=Activeworkbook.name[/COLOR]
dtTestDate = dtTestDate - 1
On Error GoTo 0
Wend


If ActiveWorkbook.Name = sStartWB Then MsgBox "Earlier file not found."


end sub


heres my formula for vlookup, the date here should be based from the result above.


Dim LR As Long


LR = Range("E" & Rows.Count).End(xlUp).Row
Range("Q2").Select
[COLOR=#ff0000]Range("Q2:Q" & Lr).FormulaR1C1 = "=IF(VLOOKUP(RC[-8],'[" & tst & "]Sheet1'!C8,1,FALSE),""ARGUS"")"[/COLOR]

You seem to be using vlookup just to check whether a value is in a list, it should be faster and more efficient to use match or countif

 

[mwalls33]

I think you only need to make a small change as:

Workbooks.Open sPath & "IPNS Security " & Format(dtTestDate, "MMDDYYYY") & ".xlsx"
[COLOR=#ff0000]tst=Activeworkbook.name[/COLOR]
dtTestDate = dtTestDate - 1
On Error GoTo 0
Wend


If ActiveWorkbook.Name = sStartWB Then MsgBox "Earlier file not found."


end sub


heres my formula for vlookup, the date here should be based from the result above.


Dim LR As Long


LR = Range("E" & Rows.Count).End(xlUp).Row
Range("Q2").Select
[COLOR=#ff0000]Range("Q2:Q" & Lr).FormulaR1C1 = "=IF(VLOOKUP(RC[-8],'[" & tst & "]Sheet1'!C8,1,FALSE),""ARGUS"")"[/COLOR]

You seem to be using vlookup just to check whether a value is in a list, it should be faster and more efficient to use match or countif

Thanks so much for responding! I'm copyng and pasting my codes here - when I changed it wouldn't work and it just hangs?

Sub Open_Argus()
'
' Open Latest Argus File

'---Opens a sheet based on date, searches backward from today til it finds a matching date
Dim dtTestDate As Date
Dim sStartWB As String

Const sPath As String = "V:\Security\ARGUS\Argus Users Lists\2018"
dtEarliest = Date - 5
'--to stop loop if file not found by earliest valid date.
dtTestDate = Date
sStartWB = ActiveWorkbook.Name

While ActiveWorkbook.Name = sStartWB And dtTestDate >= dtEarliest
On Error Resume Next
Workbooks.Open sPath & "IPNS Security " & Format(dtTestDate, "MMDDYYYY") & ".xlsx"
dtTestDate = dtTestDate - 1
On Error GoTo 0
Wend

If ActiveWorkbook.Name = sStartWB Then MsgBox "Earlier file not found."

End Sub
Sub ArgusLookUp()
'
' argusvlookup Macro
' need to find a formula for dynamic filename
'You need to change the date below (5th row) to match ARGUS filename
Dim LR As Long

LR = Range("E" & Rows.Count).End(xlUp).Row
Range("Q2").Select
Range("Q2:Q" & LR).FormulaR1C1 = _
"=IF(VLOOKUP(RC[-8],'[IPNS Security 07232018.xlsx]Sheet1'!C8,1,FALSE),""ARGUS"")"
Dim xRg As Range
Dim xCell As Range
Dim i As Long
Dim j As Long
Dim K As Long

Range("A1").Select
i = Worksheets("Sheet1").UsedRange.Rows.Count
j = Worksheets("ARGUS").UsedRange.Rows.Count
If j = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("ARGUS").UsedRange) = 0 Then j = 0
End If
Set xRg = Worksheets("Sheet1").Range("Q2:Q" & i)
On Error Resume Next
Application.ScreenUpdating = False
For K = 1 To xRg.Count
If CStr(xRg(K).Value) = "ARGUS" Then
xRg(K).EntireRow.Copy Destination:=Worksheets("ARGUS").Range("A" & j + 1)
xRg(K).EntireRow.Delete
If CStr(xRg(K).Value) = "Done" Then
K = K - 1
End If
j = j + 1
End If
Next
Application.ScreenUpdating = True
Columns("Q:Q").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select

End Sub

 

[offthelip]

You haven't made the change that I suggested, you appear to have made or at least posted a whole lot of other code, you haven't given us any clue as to where it "hangs", so it is kind of difficult to help you.
I suggest you run the macro with a break point right at the top and then use F8 to step through the macro a line at a time to see where it hangs. Is there some reason you didn't like my solution to your original question?