Range Lookup Problem In VBA

[zaska]

Hi,

code:

    ChDir "E:\Macros\Input"
    Workbooks.Open Filename:="E:\Macros\Input\fo" & Format(Workbooks("NSE Converter.xls").Sheets("Sheet1").Range("G2").Value, "ddmmmyyyy") & "bhav.csv", Origin:=xlWindows
    Application.ScreenUpdating = False
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
    With Range("A1:O" & LastRow)
     .AutoFilter Field:=1, Criteria1:="<>FUTIDX", Operator:=xlAnd, Criteria2:="<>FUTSTK"
     .Offset(1, 0).EntireRow.Delete
    End With
    ActiveSheet.AutoFilterMode = False
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Columns("C").Insert
    Range("C2").Value = "-I"
    For i = 3 To LR
    If Range("B" & i).Value = Range("B" & i - 1).Value Then
        Range("C" & i).Value = Range("C" & i - 1).Value & "I"
    Else
        Range("C" & i).Value = "-I"
    End If
    Next i
    For i = 2 To LR
    Range("B" & i).Value = Range("B" & i).Value & Range("C" & i).Value
    Range("D" & i).NumberFormat = "yyyymmdd"
    Range("D" & i).Value = Range("P" & i).Value
    Next i
    Columns("C").Delete
    Columns("N:O").Delete
    Columns("L").Delete
    Columns("J").Delete
    Columns("D:E").Delete
    Columns("A").Delete
    Range("B1").Value = "TIMESTAMP"
    Range("I2:I" & LR).Formula = "=G2*LOOKUP(9.99999999999999E+307,SEARCH(""#""&'[NSE Converter.xls]Sheet1'!$A$1:$A$236,""#""&SUBSTITUTE(TRIM(A2),CHAR(160),"""")),'[NSE Converter.xls]Sheet1'!$B$1:$B$236)"
    For i = 2 To LR
    Range("G" & i).Value = Range("I" & i).Value
    Next i
    Columns("I").Delete
    Rows("1").Delete
    Application.ScreenUpdating = False
    ChDir "E:\Macros\Output"
    ActiveWorkbook.SaveAs Filename:="E:\Macros\Output\Stocks\fo" & Format(Date, "ddmmmyyyy") & "bhav.txt", FileFormat:=xlCSV, CreateBackup:=False
    Application.DisplayAlerts = False
    Application.ScreenUpdating = True
    ActiveWindow.Close
    Application.ScreenUpdating = False

Problem:

    Range("I2:I" & LR).Formula = "=G2*LOOKUP(9.99999999999999E+307,SEARCH(""#""&'[NSE Converter.xls]Sheet1'!$A$1:$A$236,""#""&SUBSTITUTE(TRIM(A2),CHAR(160),"""")),'[NSE Converter.xls]Sheet1'!$B$1:$B$236)"

The data which should be looked up by the formula in Sheet1 is stored in the following Link.

http://www.nseindia.com/content/fo/fo_mktlots.csv

Every day i am manually downloading the data from the link and pasting the values in Sheet1 as well as changing the lookup reference range depending on the number of cells in sheet1.

I kindly request anyone to assist me with VBA code which can download the file from the above link and the same can be used by the lookup formula.

Thank you

 

[zaska]

Can anyone help me with the above situation?

Thanks

 

[zaska]

Can anyone look at this code?

Thanks