Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Sep 2001
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Read one Cell and Execute Action (Excel 97)

    Hello people,

    Hope someone can help me with this problem. I'm trying to write a macro so that it will read the cells in Row A, and if it contains the word "total", then copy in the cell on the next column, the contents in the cell 2 rows up from the cell that I want copy the new data. Hope I haven't confused everyone.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Read one Cell and Execute Action (Excel 97)

    Like this?

    Sub align2down()
    Dim rngCell As Range
    For Each rngCell In ActiveSheet.UsedRange.Columns("A:A"). _
    SpecialCells(xlCellTypeConstants, xlTextValues)
    If LCase(rngCell.Value) = "total" Then rngCell.Offset(-2, 1).Copy rngCell.Offset(0, 1)
    Next rngCell
    End Sub

    This copies, it doesn't move.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Lounger
    Join Date
    Sep 2001
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Read one Cell and Execute Action (Excel 97)

    Tell me if I'm wrong, but I think this is reading for the cell to equal "total". Is it possible for the macro to read that the cell contains the word "Total", because the cell contains other information as well.

    Thanks for the help.

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

    Re: Read one Cell and Execute Action (Excel 97)

    Change the If statement to:

    <pre> If InStr(LCase(rngCell.Value), "total") Then rngCell.Offset(-2, 1).Copy rngCell.Offset(0, 1)
    </pre>

    Legare Coleman

  5. #5
    Lounger
    Join Date
    Sep 2001
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Read one Cell and Execute Action (Excel 97)

    I don't know why but it is just not working for me. The word that I'm looking for is "Total", does the captial "T" make a difference?

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

    Re: Read one Cell and Execute Action (Excel 97)

    No, the capital T should not make a difference. The LCase function should convert whatever in in the cell to lower case, so "total" should match any combination of upper and lower case.

    Can you attach a copy of the workbook so we can take a look?
    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
  •