Read one Cell and Execute Action (Excel 97)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

Change the If statement to:

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

Reply | Quote

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?

Reply | Quote

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?

Reply | Quote