Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    cells.find (Excel 97)

    I am trying to write a simple macro to clean up a column of data that goes something like this.

    'go to first record in Hospital ID column
    Range("A1").Select

    'search for 1st instance of "#N/A". I NEED TO EVALUATE THE RESULTS OF THIS CELL.FIND
    ' TO SEE IF IT FOUND AN EMPTY CELL (see 'question below)
    Cells.Find(What:="#N/A", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
    .Activate

    'search for 1st instance of a valid Hospital ID number after the 1st instance of #N/A
    'hospital IDs always begin with one.
    Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
    False).Activate

    'Copy the valid Hospital ID number
    Selection.Copy

    'go to first record in Hospital ID column
    Range("A1").Select

    'search for 1st instance of #N/A
    Cells.Find(What:="#N/A", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
    .Activate

    'paste the value of the valid hospital id into the cell.
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False

    'turn off the cut,copy,paste mode
    Application.CutCopyMode = False

    How do I put this in some kind of Do Loop until the first Cell.Find finds an empty cell?

    Thanks,
    aap2

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: cells.find (Excel 97)

    I would do it like this:

    <pre>Public Sub CleanUp()
    Dim oCell As Range
    Dim I As Long, J As Long, lRowMax As Long
    lRowMax = Worksheets("Sheet1").Range("A65536").End(xlUp).Row
    With Worksheets("Sheet1").Range("A1")
    For I = 0 To lRowMax - 1
    If IsError(.Offset(I, 0)) Then
    For J = I + 1 To lRowMax - 1
    If Not IsError(.Offset(J, 0)) Then
    If Left(.Offset(J, 0), 1) = "1" Then
    .Offset(I, 0) = .Offset(J, 0)
    Exit For
    End If
    End If
    Next J
    End If
    Next I
    End With
    End Sub
    </pre>

    Legare Coleman

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,824
    Thanks
    135
    Thanked 482 Times in 459 Posts

    Re: cells.find (Excel 97)

    what happens if the last entry is #N/A?
    what happens if there are no valid Hospital IDs (i.e. begiining with "1") below the current #N/A cell?

    zeddy

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: cells.find (Excel 97)

    In either case, the #NA will not be changed. If he wants to insure that this does not happen, then he can make sure that the last thing in column A has a 1 in the first position. Something like "1 No valid ID" might be appropriate.
    Legare Coleman

  5. #5
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: cells.find (Excel 97)

    Thank you for your suggestion. Can you educate me a little here please? Have a look at my comment lines and please tell me if I understand what you are doing here.
    Many thanks,
    aap2


    Public Sub CleanUp()
    Dim oCell As Range
    Dim I As Long, J As Long, lRowMax As Long

    ' I think this line tells me the range of cells that will be operated on. A1 to A65536.
    ' Is 65536 an excel default for the maximum number of rows that can be in
    ' a column or did you pick a high number hoping to include the end of data?
    ' does the .End(xlUp) make the program proceed from the bottom up?
    lRowMax = Worksheets("Sheet1").Range("A65536").End(xlUp).Row

    ' start at the A1 cell and do some stuff
    With Worksheets("Sheet1").Range("A1")

    ' set up an iterative process to systematically go through each cell one at a time and check
    ' for "IsError". By the way, why the -1 on the lRowMax?
    For I = 0 To lRowMax - 1

    ' IsError would indicate an empty cell I think.
    If IsError(.Offset(I, 0)) Then

    ' if an empty cell is found, the IsError is true and this second search happens.
    For J = I + 1 To lRowMax - 1
    If Not IsError(.Offset(J, 0)) Then

    ' if this is not an empty cell, check to see if this is a valid hospital ID.
    If Left(.Offset(J, 0), 1) = "1" Then

    ' if it is a valid hospital ID, the set the value in the previously found cell .Offset(I,0) to the
    ' value of the valid hospital ID .Offset(J,0). this keeps looping around until lRowMax - 1 is reached.
    .Offset(I, 0) = .Offset(J, 0)

    ' exit the inner for statement that looped around until lRowMax - 1 was reached
    Exit For

    ' exit the If statement that looked for an empty cell
    End If
    End If
    Next J
    End If
    Next I
    End With
    End Sub

  6. #6
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: cells.find (Excel 97)

    These are two very good questions. As we pull these data from one system, it is consistent that the last record is not #NA. However, to be safe, I suppose I should put something in place that evaluates the last record to be sure.
    Thanks,
    aap2

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: cells.find (Excel 97)

    1- lRowMax is the number of rows that will be operated on. A65536 is the last possible cell in a column, and the .End(xlUp) finds the last row that contains data.

    2- The With statement sets the default to use with other properties and methods like .Offset. It lets you type .Offset(I,0) instead of Worksheets("Sheet1").Range("A1").Offset(I,0)

    3- Yes, step I from to lRowMax-1 to step through the rows to be processed. The -1 is because lRowMax contains the last row number, and row numbers start at 1 but the Offset property starts at 0.

    4- No, IsError indicated that the cell contains an error value like #NA, #DIV, etc.

    5- If an error value like #NA is found, then start searching on the next row for a valid ID number.

    6- If it is not an error value then look for a valid ID number.

    7- Yes

    8- Yes
    Legare Coleman

  8. #8
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: cells.find (Excel 97)

    Thanks for your help. The macro works. I had one other question. Because you declared this as "Public Sub..." does that mean that I can run it from a command button on a form or toolbar? I would like to build this into a command button that cleans up the monthly dataset.

    aap2

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: cells.find (Excel 97)

    Yes, you should be able to run this from a command button.
    Legare Coleman

Posting Permissions

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