Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Searching through sheets - VBA (2003 SP2)

    I wish to search through a master sheet for a policy number, I then want to find that policy number in a range of sheets and return the inception date of the policy as per the range of sheets in the row of the master sheet.

    My code is as follows:

    Dim lngMaxRow As Long, lngRow As Long, PolNo, Mycell
    Sheets("MASTER").Select

    lngMaxRow = Range("G65536").End(xlUp).Row
    For lngRow = lngMaxRow To 11 Step -1 'Row 11 is where it stops
    If Cells(lngRow, 28) = "" Then 'The contents of Column 28 is tested to ensure it is blank :. Has not been processed

    PolNo = Cells(lngRow, 7) 'Grabs the Policy Number
    Sheets(Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")).Select
    With Selection
    Set c = .Find(PolNo)
    Mycell = Range(ActiveCell.Address()).Row
    Cells(lngRow, 22).Value = Cells(Mycell, 4).Value
    End With

    Next lngRow


    I can see that the code picks up the correct date (Mycell = Range(ActiveCell.Address()).Row is correct) however it seems to get lost in the process and doesn

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Searching through sheets - VBA (2003 SP2)

    What value is it supposed to be returning? You don't do anything with <code>c</code> which is the range where the policy number is found.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching through sheets - VBA (2003 SP2)

    Thanks for replying so quickly. What I want is for the 4th column of the row in which the policy number is found (in one of the Jan to Dec sheets) to be copied to the 22nd column of the row where the code began, ie on the master sheet. This is what I am attempting to accomplish by the code:
    Cells(lngRow, 22).Value = Cells(Mycell, 4).Value

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Searching through sheets - VBA (2003 SP2)

    Try this:

    <pre>Sub FindIt()

    Dim lngMaxRow As Long, lngRow As Long, PolNo, Mycell, c As Range
    Dim varItem, varSheets
    Sheets("MASTER").Select

    lngMaxRow = Range("G65536").End(xlUp).Row
    For lngRow = lngMaxRow To 11 Step -1 'Row 11 is where it stops
    If Cells(lngRow, 28) = "" Then
    'The contents of Col 28 is tested to ensure it is blank :has not been processed

    PolNo = Cells(lngRow, 7) 'Grabs the Policy Number
    varSheets = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul")
    For Each varItem In varSheets
    With Sheets(varItem)
    Set c = .UsedRange.Find(PolNo)
    If Not c Is Nothing Then
    Sheets("MASTER").Cells(lngRow, 22).Value = .Cells(c.Row, 4).Value
    Exit For
    End If

    End With
    Next varItem
    End If
    Next lngRow

    End Sub

    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching through sheets - VBA (2003 SP2)

    Fantastic, thanks rory, works great.
    For the benefit of anyone else reading this thread, I discovered you can also add formulas using this method, such as:

    Sheets("MASTER").Cells(lngRow, 22).Value = .Cells(c.Row, 4).Value - Sheets("MASTER").Cells(lngRow, 20).Value

    Very helpful. Thanks again Rory.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •