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

hutch27

New Member
Joined
May 5, 2014
Messages
37
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Try this.
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.Offset(1).Value = cell.Value
            cell.Value = "Total
        End If
    Next cell

End Sub
 
Upvote 0
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.
 
Last edited:
Upvote 0
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)'.
 
Upvote 0

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top