none
How to loop through a range and find a value in a large workbook? RRS feed

  • Question

  •  I have a rather large workbook that I’m trying to get certain values from.  I have a bunch of stock tickers in ColumnC of my ‘Pool’ sheet.  I’m trying to loop through these tickers (Range C6:C180) and then loop through all the sheets (there are a lot) and find two yields on each sheet, then copy/paste them to Column M & N.  I have the code below working to find the two yields.  I’m not sure how to grab the ticker, find it in the correct sheet (the ticker is always in cell F3 of each sheet) and then run my code below.  Does anyone have a suggestion as to how to do this? 

     

    Dim oSht As Worksheet

    Dim lastRow As Long, i As Long

    Dim strSearch As String

    Dim t As Long

    Dim aCell As Range

       

     

        t = GetTickCount

        Set oSht = ActiveSheet

        lastRow = oSht.Range("B" & Rows.Count).End(xlUp).Row

        strSearch = "YIELD COMPUTATION"

        Set aCell = oSht.Range("B1:B" & lastRow).Find(What:=strSearch, LookIn:=xlValues, _

        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _

        MatchCase:=False, SearchFormat:=False)

        aCell.Offset(2, 6).Select

        YieldOne = ActiveCell.Address

        YieldOneValue = Range(YieldOne).Value

     

      

        t = GetTickCount

        Set oSht = ActiveSheet

        lastRow = oSht.Range("B" & Rows.Count).End(xlUp).Row

        strSearch = "NOTE YIELD COMPUTATION"

        Set aCell = oSht.Range("B1:B" & lastRow).Find(What:=strSearch, LookIn:=xlValues, _

        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _

        MatchCase:=False, SearchFormat:=False)

        aCell.Offset(2, 6).Select

        YieldTwo = ActiveCell.Address

        YieldTwoValue = Range(YieldTwo).Value

     

     

    The worksheets are generated dynamically, so I need a VBA script to do this work. 

    Thanks.



    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, May 29, 2015 4:57 PM

Answers

  • Hi ryguy72,

    >>However, let's say I want to link the results on the Summary sheet to the different sheets, rather than hard code everything.  How can I do that?  <<

    Did you want to make the code works for other worksheets. If I understood correctly, we can add a parameter for this sub to pass the name of worksheet we want to handle. Here is an example that for your reference:

    Sub test(sheetName As String)
    Dim targetWorksheet As Worksheet
    Set targetWorksheet = Worksheets(sheetName)
    'use  targetWorksheet to handle the business 
    End Sub

    If I misunderstood, please feel free to let me know.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, June 1, 2015 7:43 AM
    Moderator

All replies

  • Ok, I got it working!!

    Sub TheYields()
    
    
    On Error Resume Next
    
        Dim MyCell As Range
        Dim MyRange1 As Range
        Dim MyRange2 As Range
         
        Set MyRange1 = Sheets("Summary").Range("C6")
        Set MyRange1 = Range(MyRange1, MyRange1.End(xlDown))
    
        For Each MyCell In MyRange1
        SelectSheet = MyCell.Offset(0, -1).Value
            Sheets(SelectSheet).Select
       
                Dim oSht As Worksheet
                Dim lastRow As Long, i As Long
                Dim strSearch As String
                Dim t As Long
                Dim aCell As Range
                
                
                t = GetTickCount
                Set oSht = ActiveSheet
                lastRow = oSht.Range("B" & Rows.Count).End(xlUp).Row
                strSearch = "YIELD COMPUTATION"
                Set aCell = oSht.Range("B1:B" & lastRow).Find(What:=strSearch, LookIn:=xlValues, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)
                aCell.Offset(2, 6).Select
                YieldOne = ActiveCell.Address
                YieldOneValue = Range(YieldOne).Value
                
                t = GetTickCount
                Set oSht = ActiveSheet
                lastRow = oSht.Range("B" & Rows.Count).End(xlUp).Row
                strSearch = "NOTE YIELD COMPUTATION"
                Set aCell = oSht.Range("B1:B" & lastRow).Find(What:=strSearch, LookIn:=xlValues, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)
                aCell.Offset(2, 6).Select
                YieldTwo = ActiveCell.Address
                YieldTwoValue = Range(YieldTwo).Value
    
                MyCell.Offset(0, 10).Value = YieldOneValue
                MyCell.Offset(0, 11).Value = YieldTwoValue
                
        Next MyCell
    
    
    Worksheets("Summary").Select
    
    End Sub

    However, let's say I want to link the results on the Summary sheet to the different sheets, rather than hard code everything.  How can I do that?  I know the key is here.

    MyCell.Offset(0, 10).Value = YieldOneValue
    MyCell.Offset(0, 11).Value = YieldTwoValue

    How can I make those link to the sheet?


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.


    • Edited by ryguy72 Friday, May 29, 2015 9:04 PM
    Friday, May 29, 2015 9:04 PM
  • Hi ryguy72,

    >>However, let's say I want to link the results on the Summary sheet to the different sheets, rather than hard code everything.  How can I do that?  <<

    Did you want to make the code works for other worksheets. If I understood correctly, we can add a parameter for this sub to pass the name of worksheet we want to handle. Here is an example that for your reference:

    Sub test(sheetName As String)
    Dim targetWorksheet As Worksheet
    Set targetWorksheet = Worksheets(sheetName)
    'use  targetWorksheet to handle the business 
    End Sub

    If I misunderstood, please feel free to let me know.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, June 1, 2015 7:43 AM
    Moderator