+ Reply to Thread
Results 1 to 4 of 4

VBA code not pulling info on first occurance only

Hybrid View

  1. #1
    Registered User
    Join Date
    08-14-2013
    Location
    Arizona
    MS-Off Ver
    Excel 2007
    Posts
    71

    VBA code not pulling info on first occurance only

    Hi all, I'm having issues with a VBA. I am looking up data in cell L1, compared to col B, then bringing the data from matching rows, col E. Problem is that it pulls the data from col E pastes in col M, gives row number in col N, and col O is supposed to show col I. This works for everything BUT O1 stays blank. The rest of col O populates, but not O1

        Dim c As Range, r As Long, addr
        Set c = Columns("B").Find(Range("L1").Value, lookat:=xlWhole, MatchCase:=False)
        If c Is Nothing Then
        Unload Me
        MsgBox "Could not find a date for the requested name."
        Exit Sub
        Else
        If Not c Is Nothing Then
            addr = c.Address
            r = Cells(Rows.Count, "M").End(xlUp).Row
            If r <> 1 Then r = r + 1
            Cells(r, "M").Value = Cells(c.Row, "E").Value
            Cells(r, "N").Value = c.Row
            r = r + 1
            Do
                Set c = Columns("B").FindNext(c)
                If Not c Is Nothing Then
                    If addr <> c.Address Then
                        Cells(r, "M").Value = Cells(c.Row, "E").Value
                        Cells(r, "O").Value = Cells(c.Row, "I").Value
                        Cells(r, "N").Value = c.Row
                        r = r + 1
                    End If
                End If
            Loop While addr <> c.Address And Not c Is Nothing
            
    End If
    End If

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: VBA code not pulling info on first occurance only

    Hello jwlamb,

    This should solve your problem.
        Dim addr    As String
        Dim c       As Range
        Dim r       As Long
        
            Set c = Columns("B:B").Find(Range("L1").Value, , xlValues, xlWhole, xlByRows, xlNext, False, False, False)
            If c Is Nothing Then
                Unload Me
                MsgBox "Could not find a date for the requested name."
                Exit Sub
            End If
            
                addr = c.Address
                r = Cells(Rows.Count, "M").End(xlUp).Row
                
                If r > 1 Then r = r + 1
                
                Do
                    Cells(r, "M").Value = Cells(c.Row, "E").Value
                    Cells(r, "O").Value = Cells(c.Row, "I").Value
                    Cells(r, "N").Value = c.Row
                    r = r + 1
                    Set c = Columns("B:B").FindNext(c)
                    If c Is Nothing Then Exit Do
                    If c.Address = addr Then Exit Do
                Loop
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    08-14-2013
    Location
    Arizona
    MS-Off Ver
    Excel 2007
    Posts
    71

    Re: VBA code not pulling info on first occurance only

    YES! Thank you very much!

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: VBA code not pulling info on first occurance only

    Hello jwlamb,

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Pulling Info from Column
    By SouthFloridaBadger in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-08-2014, 11:30 AM
  2. pulling info from spreadsheet
    By shep2259 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-05-2014, 12:51 PM
  3. Excel 2007 : Pulling info off 1 sheet from another
    By kwrcst in forum Excel General
    Replies: 2
    Last Post: 05-11-2012, 10:17 AM
  4. SUM IFS Help pulling from table of info
    By Merlin54k in forum Excel General
    Replies: 3
    Last Post: 08-10-2011, 12:04 PM
  5. Pulling info into another sheet
    By Teric506 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-26-2005, 01:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1