IF column "I" contains certain text, move to cell below and replace the original cell with "Total"

[hutch27]

Hi all,

So I'm stuck with getting my cut/paste vba to work as well as a separate question. Here are my criteria:

• Column "I" contains only one instance of the word "Final".
• Need to cut/paste "Final" so it moves 1 cell below.
• Need to insert the word "Total" into the original location of the word "Final"
• Outcome should be the word "Total" with the word "Final" below it.

Here is what I have regarding the cut/paste VBA code:

Sub Macro14()
Dim rngA As Range
Dim cell As Range


Set rngA = Sheets("GL Detail").Range("I:I")
For Each cell In rngA
If cell.Value = "Final Difference (after factoring in Distributions/Withholding Taxes Payable)" Then
cell.Cut
Sheets("GL Detail").Range("I1:I").End(xlDown).Select
ActiveSheet.Paste
End If
Next cell


End Sub

 

[Norie]

Try this.

Sub Macro14()
Dim rngA As Range
Dim cell As Range


    Set rngA = Sheets("GL Detail").Range("I:I")
    For Each cell In rngA
        If cell.Value = "Final Difference (after factoring in Distributions/Withholding Taxes Payable)" Then
            cell.Offset(1).Value = cell.Value
            cell.Value = "Total
        End If
    Next cell

End Sub

 

[hutch27]

Sorry - I messed up my question a little bit:

Here are my criteria:

• Column "I" contains only one instance of the word "Final Difference (after factoring in Distributions/Withholding Taxes Payable)".
• Need to cut/paste "Final Difference (after factoring in Distributions/Withholding Taxes Payable)" so it moves 1 cell below.
• Need to insert the word "Total" into the original location of the word "Final Difference (after factoring in Distributions/Withholding Taxes Payable)"
• Outcome should be the word "Total" with the word "Final Difference (after factoring in Distributions/Withholding Taxes Payable)" below it.

Your edited code inserts the word "Total" into almost every row. I think because the range is defined at the entire column "I", however, I can't think of another way to define the range, since it's variable.

 

[Norie]

The code I posted will only put Total in the cell where 'Final Difference (after factoring in Distributions/Withholding Taxes Payable)' and put 'Final Difference (after factoring in Distributions/Withholding Taxes Payable)'.

It won't do anything else unless it finds multiple instances of 'Final Difference (after factoring in Distributions/Withholding Taxes Payable)'.