Change from copy to cut and paste VBA.

[Livin404]

Good evening, I had some help getting to this point. Now I only need to tweak the code so rather than copy I need it to remove the value from L and place in M. The biggest problem I'm having is when it cuts it leaves the now empty cell without its filled color. I don't want this thing to touch the format.

Here is the code:

Sub OffsetValue()
    ' Declare variables
    Dim lastRow As Long
    Dim currentRow As Long
    Dim currentValue As String

    lastRow = Cells(Rows.Count, "C").End(xlUp).Row

    For currentRow = 5 To lastRow
        ' Get the value in column C for the current row
        currentValue = Cells(currentRow, "C").Value


        If Mid(currentValue, 6, 2) = "TD" Or Mid(currentValue, 6, 2) = "TE" Then
           
            If Not IsEmpty(Cells(currentRow, "L").Value) Then
                ' If  copy the value in column L to column M
                Cells(currentRow, "M").Value = Cells(currentRow, "L").Value

            End If
        End If
    Next currentRow
End Sub

 

[Joe4]

This is the line of code that copies the value from column L to column M:

Cells(currentRow, "M").Value = Cells(currentRow, "L").Value

If you want to then clear the value from column L without removing its formatting, add this line underneath it:

Cells(currentRow, "L").ClearContents

 

[Livin404]

This is the line of code that copies the value from column L to column M:

Cells(currentRow, "M").Value = Cells(currentRow, "L").Value

If you want to then clear the value from column L without removing its formatting, add this line underneath it:

Cells(currentRow, "L").ClearContents

Thank you for getting back. I did try that and it does copy the first value, but when it comes to clearing L I get an error message telling me "We can't do that to a Merged Cell".
I did put a unmerge statement for the current row. However I'm trying to figure where I can put the Merge statement back in.

below in an extract of the statement. The middle line is what I added in.

Cells(currentRow, "M").Value = Cells(currentRow, "L").Value
                Cells(currentRow, "L").UnMerge
                Cells(currentRow, "L").ClearContents

Thank you so much!

 

[Joe4]

I presume you would just re-merge whatever you want after the ClearContents step.
You can probably use the Macro Recorder to record yourself doing that merge to get an idea of what that code needs to look like.

However, there is something I should note, and I will try to put this as delicately as possible:
"Merged cells are the spawn of the devil and should be avoided at all costs!!!"

It is probably the single worst feature of Excel. It causes nothing but trouble for things like VBA and sorting (as matter as fact, I know many MVP who won't even look at any question that involve merged cells!).

If you are merging cells across columns in a single row, it is far better to use the "Center as Selection" formatting option, as explained here: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis
Using this option instead avoids all the issues merged cells represent.

 

[Livin404]

Thank you, I will go with your first response. It achieved what I said after I added my little line. Not remerging the lines which the numbers were extracted from is not a huge deal. This thing will be used on a daily bases. At a later date I may return to it if my OCPD gets the best of me. Nonetheless, you set me up perfectly. I think you mentioned before about the merging of cells. I can't see how one lives without it especially when it comes to sequencing or lining things up.

 

[Joe4]

I think you mentioned before about the merging of cells. I can't see how one lives without it especially when it comes to sequencing or lining things up.

Most people do manage to live without it! I have been using Excel for 30 years and rarely have need for it, especially merging across rows.
As I mentioned, their is an alternative solution to merging across columns that works and does not have issues.

If you were only doing simple things (for visual/presentation purposes), it would not be a big deal.
But if you want to involve VBA or make it interactive and want to do things like sorting and filtering, then you may run into issues.
Once you get used to NOT using them, it is not a big deal. Like breaking any kind of bad habit, I guess!

 

[Livin404]

Most people do manage to live without it! I have been using Excel for 30 years and rarely have need for it, especially merging across rows.
As I mentioned, their is an alternative solution to merging across columns that works and does not have issues.

If you were only doing simple things (for visual/presentation purposes), it would not be a big deal.
But if you want to involve VBA or make it interactive and want to do things like sorting and filtering, then you may run into issues.
Once you get used to NOT using them, it is not a big deal. Like breaking any kind of bad habit, I guess!

I'll have to Google it or YouTube the alternatives for merging. Thank you,

 

[Joe4]

I'll have to Google it or YouTube the alternatives for merging. Thank you,

I posted a link to one in post #4.

 

[Livin404]

Thank you I already bookmarked it for future reference.

Enjoy the rest of your weekend.