VLOOKUP Macro must be run twice

[dhd2005]

Hello,

I have a macro whose primary task is to add VLOOKUP formulas to Columns E and F.

When the macro is run for the first time, the VLOOKUP returns #N/A, but if I run it again without making any modifications, the VLOOKUP function works perfectly.

The code is as follows:

Sub ValuesTest()
'
' ValuesTest Macro
'

'
    Range("E1") = "=VLOOKUP($A1,'https://my sharepoint address/[Vendor_Information.xlsx]Sheet1'!$C$2:$H$150,4,FALSE)"
    Range("F1") = "=VLOOKUP($A1,'https://my sharepoint address/[Vendor_Information.xlsx]Sheet1'!$C$3:$H$150,5,FALSE)"
    Range("E1:F1").Select
    Selection.AutoFill Destination:=Range("E1:F6")
    
    Set Rng = Cells.Find(What:="Vlookup", _
    After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, _
    MatchCase:=False)
    
    If Not Rng Is Nothing Then
    Do
    Rng.Formula = Rng.Value
    Set Rng = Cells.FindNext(Rng)
    Loop Until Rng Is Nothing
    End If
        
    
End Sub

Additionally, when I stop the code at the "Set Rng" line on the first run, it returns the correct VLOOKUP value.

Does anyone know why the macro would have to be run multiple times to keep the formula from returning a #N/A value?

Thanks in advance,
David

[MarvinP]

This sounds like a timing problem to me. It may be because the url has not completely opened to do the vlookup. Read http://en.allexperts.com/q/Visual-Ba...s-Function.htm

I'd try to do a DoEvents after the first vlookup and see what happens. If that doesn't do it, is there a way to open the url path before doing the vlookup?

[dhd2005]

Hi Marvin,

Thank you for the quick response and great suggestion! So this does help - but only if that part of the code stands alone. For example, the following code works:

Sub ValuesTest()
'
' ValuesTest Macro
'
'
    Range("F1") = "=VLOOKUP($A1,'https://sharepoint_site/[Vendor_Information.xlsx]Sheet1'!$C$2:$H$150,4,FALSE)"
    Range("G1") = "=VLOOKUP($A1,'https://sharepoint_site/[Vendor_Information.xlsx]Sheet1'!$C$3:$H$150,5,FALSE)"
    Range("F1:G1").Select
    Dim lrow As Long
    lrow = Cells(Rows.Count, 2).End(xlUp).Row
    Range("F1").AutoFill Destination:=Range("F1:F" & lrow)
    Range("G1").AutoFill Destination:=Range("G1:G" & lrow)
        
    Dim i As Long
    m_cancel = False
    For i = 1 To 10000
    Caption = i
    DoEvents
    If m_cancel Then Exit For
    Next
    
    Set Rng = Cells.Find(What:="Vlookup", _
    After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, _
    MatchCase:=False)
    
    If Not Rng Is Nothing Then
    Do
    Rng.Formula = Rng.Value
    Set Rng = Cells.FindNext(Rng)
    Loop Until Rng Is Nothing
    End If
            
End Sub

However, when I add any actions after that (example below), it doesn't work. For example:

Sub ValuesTest()
'
' ValuesTest Macro
'

'
    Range("F1") = "=VLOOKUP($A1,'https://sharepoint_site/[Vendor_Information.xlsx]Sheet1'!$C$2:$H$150,4,FALSE)"
    Range("G1") = "=VLOOKUP($A1,'https://sharepoint_site/[Vendor_Information.xlsx]Sheet1'!$C$3:$H$150,5,FALSE)"
    Range("F1:G1").Select
    Dim lrow As Long
    lrow = Cells(Rows.Count, 2).End(xlUp).Row
    Range("F1").AutoFill Destination:=Range("F1:F" & lrow)
    Range("G1").AutoFill Destination:=Range("G1:G" & lrow)
        
    Dim i As Long
    m_cancel = False
    For i = 1 To 10000
    Caption = i
    DoEvents
    If m_cancel Then Exit For
    Next
    
    Set Rng = Cells.Find(What:="Vlookup", _
    After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, _
    MatchCase:=False)
    
    If Not Rng Is Nothing Then
    Do
    Rng.Formula = Rng.Value
    Set Rng = Cells.FindNext(Rng)
    Loop Until Rng Is Nothing
    End If
    
    Columns("A:A").Select
    Columns("A:D").Insert Shift:=xlToRight
    Columns("J:K").Select
    Selection.Cut
    Columns("E:E").Select
    Selection.Insert Shift:=xlToRight
  
    Columns("G:G").Select
    Selection.Delete Shift:=xlToLeft
    Selection.Replace What:="_*", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

On the second code, the values are come back as #N/A.

I'd prefer to not have to open the spreadsheet - it's just one more step for the end user. Do I need to add additional DoEvent instances throughout the code? Any recommendations?

Thanks again for your help!