用于根据特定单元格条件将单元格从一列移动到另一列的 VBA 代码 [英] VBA code for moving cells from one column to another based on specific cell criteria

问题描述

我看到过几个问题，询问使用 VBA 将单元格从一个工作簿移动到另一个工作簿或一个工作簿到另一个工作簿，但我希望根据特定条件将信息从同一工作表中的一列移动到另一列.

I've seen several questions asking about moving cells from one workbook to another or one sheet to another using VBA, but I'm hoping to move information from one column to another in the same sheet based on specific criteria.

我编写了此代码以将包含保存"一词的单元格从 A 列移动到同一工作表中的 I 列:

I wrote this code to move cells from column A if they contained the word "save" to column I in the same sheet:

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

    Set rngA = Sheets("Jan BY").Range("A2:A1000")
    For Each cell In rngA
        If cell.Value = "save" Then
            cell.EntireRow.Cut
            Sheets("Jan BY").Range("I2").End(xlDown).Select
            ActiveSheet.Paste
        End If
    Next cell

End Sub

但是，虽然这个宏在我运行时没有显示任何错误，但它似乎也没有做任何其他事情.没有选择、剪切或粘贴任何内容.我在代码中哪里出错了?

But, while this macro doesn't display any errors when I run it, it also doesn't seem to do much of anything else, either. Nothing is selected, cut, or pasted. Where in the code did I go wrong?

推荐答案

如果包含保存"一词，则将 A 列中的单元格移动到 I 列在同一张表中

move cells from column A if they contained the word "save" to column I in the same sheet

你的代码不会做这样的事情.

Your code doesn't do anything like this.

要完成您的要求，您需要这样的东西:

To accomplish what your requirements are, you would need something like this:

Sub Findandcut()
    Dim row As Long

    For row = 2 To 1000
        ' Check if "save" appears in the value anywhere.
        If Range("A" & row).Value Like "*save*" Then
            ' Copy the value and then blank the source.
            Range("I" & row).Value = Range("A" & row).Value
            Range("A" & row).Value = ""
        End If
    Next

End Sub

<小时>

编辑

如果你想把行的全部内容移过来，让它从I列开始，只需替换相关的代码部分:

If you want to shift the entire contents of row over so it starts at column I, just replace the relevant section of code:

If Range("A" & row).Value Like "*save*" Then
    ' Shift the row so it starts at column I.
    Dim i As Integer
    For i = 1 To 8
        Range("A" & row).Insert Shift:=xlToRight
    Next
End If

这篇关于用于根据特定单元格条件将单元格从一列移动到另一列的 VBA 代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！