+ Reply to Thread
Results 1 to 7 of 7

find loop---looking for a number. works fine with letters

Hybrid View

  1. #1
    Registered User
    Join Date
    12-03-2012
    Location
    Omaha, Nebraska
    MS-Off Ver
    2010
    Posts
    39

    find loop---looking for a number. works fine with letters

    Okay, should be a quick fix, but my bug eyes are tired of looking at this. I need to find a number I know is in column r:t. I have attached the first code that looks for something in column a:e and it works fine. I have tried for 4 days now to modify it to work with column r:t and looking for a number (a phone number) . Can't get it to work. Please help. What am I missing?

    This one works:
    Dim b As Range, FoundCell As Range, LastCell As Range, lastrow As Range
    Dim ws1 As Worksheet
    Dim FirstAddr As String
    Dim lCount As Long
    Set ws1 = ThisWorkbook.ActiveSheet
    Set ws2 = ThisWorkbook.Sheets("Company")
    Set b = ws2.Range("a2:e65000")
    ws1.Unprotect "master32"
    ws2.Visible = True
    Application.ScreenUpdating = False
    
    a = InputBox("What is the Company's Name you are adding?", "Company")
    If a = "" Or a = False Then
        ws1.Range("a37").Select
        ws1.Protect "master32"
        ws2.Visible = False
        Exit Sub
    End If
    With b
        Set LastCell = .Cells(.Cells.Count)
    End With
    lCount = 0
    Set FoundCell = b.Find(what:=a, LOOKAT:=xlPart, LookIn:=xlValues, SearchDirection:=xlNext)
    If Not FoundCell Is Nothing Then
        FirstAddr = FoundCell.Address
        lCount = 1
    End If
    Do Until FoundCell Is Nothing
        FoundCell.EntireRow.Copy
        ws1.Range("a3000:a3029").SpecialCells(xlCellTypeBlanks).Cells(1).PasteSpecial
        Set FoundCell = b.FindNext(after:=FoundCell)
        If FoundCell.Address = FirstAddr Then
            Exit Do
        End If
        lCount = lCount + 1
    Loop
    This one doesn't:
    Dim lastrow As Long
    Dim gg As Range, b As Range
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim FoundCell As Range
    Dim FirstAddr As String
    Set ws1 = ThisWorkbook.ActiveSheet
    Set gg = ws1.Range("ab2997")
    ws1.Unprotect "master32"
    Application.ScreenUpdating = False
    
    If gg = "IACOMP1" Then
        Set ws2 = ThisWorkbook.Sheets("IA Company")
        ws2.Visible = True
        Set b = ws2.Range("R2:T65000")
        lCount = 0
        Set FoundCell = b.Find(what:=ws1.Range("R2998"), LOOKAT:=xlPart, LookIn:=xlValues, SearchDirection:=xlNext)
        If Not FoundCell Is Nothing Then
            FirstAddr = FoundCell.Address
            lCount = 1
        End If
        Do Until FoundCell Is Nothing
            MsgBox lCount
            'FoundCell.EntireRow.Copy
            'ws1.Range("a3000:a3029").SpecialCells(xlCellTypeBlanks).Cells(1).PasteSpecial
            Set FoundCell = b.FindNext(after:=FoundCell)
            If FoundCell.Address = FirstAddr Then
                Exit Do
            End If
            lCount = lCount + 1
        Loop
    the lcount variable is strictly me testing it.
    Foundcell consistently finds "nothing"!?!?!?!?!?
    Clearly I am missing something.

    Thank you.

  2. #2
    Registered User
    Join Date
    12-03-2012
    Location
    Omaha, Nebraska
    MS-Off Ver
    2010
    Posts
    39

    Re: find loop---looking for a number. works fine with letters

    Nobody? Anybody? Pleeeeeaaaaase?! Can not figure this one out.

  3. #3
    Valued Forum Contributor
    Join Date
    03-21-2013
    Location
    cyberia
    MS-Off Ver
    Excel 2007
    Posts
    457

    Re: find loop---looking for a number. works fine with letters

    They look like rather different codes to me. There's a lot more changes than just changing cols a:e to r:t.
    If you want to continue on the lines you're using, why not just change a:e to r:t in the first (working?) code, with only consequent other changes?

  4. #4
    Registered User
    Join Date
    12-03-2012
    Location
    Omaha, Nebraska
    MS-Off Ver
    2010
    Posts
    39

    Re: find loop---looking for a number. works fine with letters

    Thank you for the response Kalak. I did just that. The version above is just one of the many iterations I tried. I know it's an "integer" problem. I am looking up phone numbers in column r:t. I just can't figure out how to incorporate an integer into my code. "a" is essentially a phone number located in "ws1.range ("r2998"). the working code looks up string and works fine. Numbers are the issue. If i change something to "apple" in the non working code, it finds it.

  5. #5
    Registered User
    Join Date
    12-03-2012
    Location
    Omaha, Nebraska
    MS-Off Ver
    2010
    Posts
    39

    Re: find loop---looking for a number. works fine with letters

    OK. While running the following code:
    Foundcell is "nothing".
    "a" = exactly what I am looking for in column r:t.
    Which leads me to believe that the error in finding the number is related to the range object of "foundcell"
    Dim lastrow As Long
    Dim gg As Range, b As Range, foundcell As Range
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim FirstAddr As String
    Set ws1 = ThisWorkbook.ActiveSheet
    Set gg = ws1.Range("ab2997")
    ws1.Unprotect "master32"
    Application.ScreenUpdating = False
    
    If gg = "IACOMP1" Then
        Set ws2 = ThisWorkbook.Sheets("IA Company")
        ws2.Visible = True
        Set b = ws2.Range("R2:T65000")
        
        Dim abc As Double
        Dim a As Range
        Set a = ws1.Range("r2998")
    
        lCount = 0
        Set foundcell = b.Find(what:=a, LOOKAT:=xlWhole, LookIn:=xlValues, SearchDirection:=xlNext)
        If Not foundcell Is Nothing Then
            FirstAddr = foundcell.Address
            lCount = 1
        End If
        Do Until foundcell Is Nothing
            MsgBox lCount
            'FoundCell.EntireRow.Copy
            'ws1.Range("a3000:a3029").SpecialCells(xlCellTypeBlanks).Cells(1).PasteSpecial
            Set foundcell = b.FindNext(after:=foundcell)
            If foundcell.Address = FirstAddr Then
                Exit Do
            End If
            lCount = lCount + 1
        Loop
        MsgBox lCount
        GoTo 100

  6. #6
    Registered User
    Join Date
    12-03-2012
    Location
    Omaha, Nebraska
    MS-Off Ver
    2010
    Posts
    39

    Re: find loop---looking for a number. works fine with letters

    *****************
    Last edited by Dabbler39; 06-22-2013 at 06:43 PM. Reason: double post by accident

  7. #7
    Registered User
    Join Date
    12-03-2012
    Location
    Omaha, Nebraska
    MS-Off Ver
    2010
    Posts
    39

    Re: find loop---looking for a number. works fine with letters

    Finally figured it out. Only took me two weeks:
    changed
    LookIn:=xlValues
    to
    LookIn:=xlformulas

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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