locked
Dynamic Vlookup RRS feed

  • Question

  • Hi,

    I've a dataset in a sheet where I've stocks (Stock1,Stock2,Stock3,..Stock5) for given period. Now for all period I don't have stock value for all stocks. E.g. I've Stock1 data for Jan,2012-July 2012. But Stock1 value is missing for some dates, say from 15/6/2012-26/7/2012. Similarly for other stocks. Now I want to put each stock in each sheet. So, Stock1 will be in Sheet1, Stock2 will be in Sheet2, etc. For this I used the following code. The problem with this code is that I used vlookup & vlookup reference column should change for each stock. This is what I can't able to do. can anybody please help.

    Sub SrWord() Dim s As String, x As Integer Dim lCol As Long Dim rw As Long Dim LastRow As Integer Dim j As Long Dim z As Variants = "Stock1,Stock2,Stock3,Stock4,Stock5" z = VBA.Split(s, ",") For Each i In z For j = 2 To 6 lCol = WorksheetFunction.Match(i, Sheets("Sheet4").Rows("3:3"), 0)   

    Worksheets("Sheet4").Range("3:3").AutoFilter Field:=lCol, Criteria1:="<>" Worksheets(i).Columns("B").EntireColumn.Insert

    LastRow = Range("A" & Rows.Count).End(xlUp).Row

    For rw = 2 To LastRow    Sheets(i).Cells(rw, 2) = Application.VLookup(Cells(rw, 1), Sheets("Sheet7").Columns("A:G"), j, False) Next ActiveSheet.AutoFilterMode = False Next j Next i End Sub


    Thank you in Advance. Regards,



    Nemo


    • Edited by N_emo Wednesday, August 8, 2012 3:08 PM
    Wednesday, August 8, 2012 2:55 PM

Answers

  • From your macro, I have assumed that the data table is on Sheet4, starting in column A with headers in row 3. I have further assumed that the other sheets are Sheet2, 3, 5, 6, and 7, and that the stock name is in cell B1, with dates starting in A2. If any of those assumptions are incorrect, the code can easily be modified.

    Sub Macro1()
    Dim shtWSht As Worksheet

    For Each shtWSht In Sheets(Array("Sheet2", "Sheet3", "Sheet5", "Sheet6", "Sheet7"))
        With shtWSht
            With .Range("B2:B" & .Cells(.Rows.Count, 1).End(xlUp).Row)
                .FormulaR1C1 = _
                "=IF(ISERROR(MATCH(RC[-1],INDEX(Sheet4!C[-1]:C[24],," & _
                    "MATCH(R1C,Sheet4!R3,FALSE)-1),FALSE)),"""",INDEX(" & _
                    "INDEX(Sheet4!C[-1]:C[24],,MATCH(R1C,Sheet4!R3,FALSE))," & _
                    "(MATCH(RC[-1],INDEX(Sheet4!C[-1]:C[24],," & _
                    "MATCH(R1C,Sheet4!R3,FALSE)-1),FALSE))))"
                .Value = .Value
            End With
        End With
    Next shtWSht
    End Sub


    HTH, Bernie



    Wednesday, August 8, 2012 7:23 PM

All replies

  • Do you want to put each stock onto a separate sheet? Instead, you should keep all your data on one sheet, sort by data, and apply data filters to your table and choose the stock from the column of stock names. Much easier....

    HTH, Bernie

    Wednesday, August 8, 2012 3:26 PM
  • Thanks for your reply Bernie. But the task I mentioned is just small part of a big process. I need to keep the stocks in separate sheet only to work on them again. I can manually do the entire work. But the problem is it's just tad time consuming & pron to error. So want to automate it.

    Rgds,


    Nemo

    Wednesday, August 8, 2012 3:34 PM
  • Then try it this way: change the "A:A" to the column with your stock names,  and run the macro with the sheet with the data as the activesheet. I have assumed that your data table has headers.... It should fit into your data flow scheme with a few easy changes....

    Sub ExportSheetsFromDatabase()
    'Based on the values in the column A
        Dim myCell As Range
        Dim mySht As Worksheet
        Dim myName As String
        Dim myArea As Range

        Set myArea = Range("A:A")  'change this to the column with the key value
        Set myArea = Intersect(myArea, ActiveSheet.UsedRange).Offset(1, 0).Cells
        Set myArea = myArea.Resize(myArea.Rows.Count - 1, 1)

        For Each myCell In myArea
            On Error GoTo NoSheet
            myName = Worksheets(myCell.Value).Name
            GoTo SheetExists:
    NoSheet:
            Set mySht = Worksheets.Add
            mySht.Name = myCell.Value
            With myCell.CurrentRegion
                .AutoFilter Field:=1, Criteria1:=myCell.Value
                .SpecialCells(xlCellTypeVisible).Copy _
                        mySht.Range("A1")
                mySht.Cells.EntireColumn.AutoFit
                .AutoFilter
            End With
            Resume
    SheetExists:
        Next myCell
    End Sub


    HTH, Bernie



    Wednesday, August 8, 2012 3:48 PM
  • Thanks Bernie for your answer. But from your code it seems you are just copying & pasting the entire sheet for each stock in new sheet. But in that case the dates that don't have value will not be there. E.g. for Stock1 15/6/2012-26/7/2012 date will not be there. But I need to keep all the date in the new sheet as well. I tried to make vlookup dynamic using match function. Even though it's not showing error, it's not giving me the right answer

    Sub SrWord() Dim s As String, x As Integer Dim lCol As Long Dim rw As Long Dim LastRow As Integer Dim j As Long Dim z As Variants = "Stock1,Stock2,Stock3,Stock4,Stock5" z = VBA.Split(s, ",") For Each i In z lCol = WorksheetFunction.Match(i, Sheets("Sheet4").Rows("3:3"), 0)   

    Worksheets("Sheet4").Range("3:3").AutoFilter Field:=lCol, Criteria1:="<>" Worksheets(i).Columns("B").EntireColumn.Insert

    LastRow = Range("A" & Rows.Count).End(xlUp).Row

    For rw = 2 To LastRow    Sheets(i).Cells(rw, 2) = Application.VLookup(Cells(rw, 1), Sheets("Sheet7").Columns("A:G"), lCol, False) Next ActiveSheet.AutoFilterMode = False Next i End Sub

    Regards,



    Nemo

    Wednesday, August 8, 2012 5:48 PM
  • I'm sorry, I just can't help you based on your description. There is not enough detail. An example table (made up, very short) and example sheets (based on your made up table) would be helpful.

    HTH, Bernie

    Wednesday, August 8, 2012 5:55 PM
  • The raw data looks like this

    Data Stock1 Data Stock2 Data Stock3
    1/1/2012 252 1/1/2012 223 4/1/2012 216
    2/2/2012 296 2/2/2012 223 4/2/2012 219
    3/19/2012 245 2/3/2012 260 4/3/2012 291
    3/20/2012 296 2/8/2012 259 4/8/2012 276
    3/21/2012 281 2/9/2012 277 4/9/2012 299
    5/22/2012 284 3/10/2012 234 4/10/2012 280
    6/23/2012 236 3/11/2012 247 4/11/2012 274
    6/24/2012 287 3/12/2012 240 4/12/2012 250
    3/14/2012 204 4/14/2012 255
    3/15/2012 246 4/15/2012 240
    3/16/2012 230 4/16/2012 240
    3/17/2012 224 4/17/2012 286
    3/18/2012 229 4/18/2012 209
    5/19/2012 233 7/19/2012 223
    6/20/2012 219 7/20/2012 216
    6/21/2012 216 7/21/2012 247
    7/22/2012 246 7/22/2012 277
    7/23/2012 246
    7/24/2012 263

    The table for say stock1 should look like this

    Data Stock1
    1/1/2012 295
    1/2/2012
    1/3/2012  
    1/4/2012  
    1/5/2012  
    1/6/2012  
    1/7/2012  
    1/8/2012  
    1/9/2012  
    1/10/2012  
    1/11/2012  
    1/12/2012  
    1/13/2012  
    1/14/2012  
    1/15/2012  
    1/16/2012  
    1/17/2012  
    1/18/2012  
    1/19/2012  
    1/20/2012  
    1/21/2012  
    1/22/2012  
    1/23/2012  
    1/24/2012  
    1/25/2012
    1/26/2012
    1/27/2012
    1/28/2012
    1/29/2012
    1/30/2012
    1/31/2012
    2/1/2012
    2/2/2012 223
    2/3/2012
    2/4/2012
    2/5/2012
    2/6/2012
    2/7/2012
    2/8/2012
    2/9/2012
    2/10/2012
    2/11/2012
    2/12/2012
    2/13/2012
    2/14/2012
    2/15/2012
    2/16/2012
    2/17/2012
    2/18/2012
    2/19/2012
    2/20/2012
    2/21/2012
    2/22/2012
    2/23/2012
    2/24/2012
    2/25/2012
    2/26/2012
    2/27/2012
    2/28/2012
    2/29/2012
    3/1/2012
    3/2/2012
    3/3/2012
    3/4/2012
    3/5/2012
    3/6/2012
    3/7/2012
    3/8/2012
    3/9/2012
    3/10/2012
    3/11/2012
    3/12/2012
    3/13/2012
    3/14/2012
    3/15/2012
    3/16/2012
    3/17/2012
    3/18/2012
    3/19/2012 245
    3/20/2012 296
    3/21/2012 281

    But thank you for all your effort to help me. It's been very kind of you.

    Regards,


    Nemo

    Wednesday, August 8, 2012 6:17 PM
  • From your macro, I have assumed that the data table is on Sheet4, starting in column A with headers in row 3. I have further assumed that the other sheets are Sheet2, 3, 5, 6, and 7, and that the stock name is in cell B1, with dates starting in A2. If any of those assumptions are incorrect, the code can easily be modified.

    Sub Macro1()
    Dim shtWSht As Worksheet

    For Each shtWSht In Sheets(Array("Sheet2", "Sheet3", "Sheet5", "Sheet6", "Sheet7"))
        With shtWSht
            With .Range("B2:B" & .Cells(.Rows.Count, 1).End(xlUp).Row)
                .FormulaR1C1 = _
                "=IF(ISERROR(MATCH(RC[-1],INDEX(Sheet4!C[-1]:C[24],," & _
                    "MATCH(R1C,Sheet4!R3,FALSE)-1),FALSE)),"""",INDEX(" & _
                    "INDEX(Sheet4!C[-1]:C[24],,MATCH(R1C,Sheet4!R3,FALSE))," & _
                    "(MATCH(RC[-1],INDEX(Sheet4!C[-1]:C[24],," & _
                    "MATCH(R1C,Sheet4!R3,FALSE)-1),FALSE))))"
                .Value = .Value
            End With
        End With
    Next shtWSht
    End Sub


    HTH, Bernie



    Wednesday, August 8, 2012 7:23 PM
  • Thanks Bernie for your answer. It worked! I took time to reply back as I was testing your code (with some modifications). Also, my time zone is different. So by the time you replied, I was fast asleep :) Hope you don't mind my delayed response. Thanks again for your effort to solve my problem.

    Rgds,


    Nemo

    Thursday, August 9, 2012 3:52 PM