复制单元格并移动到另一个单元格(偏移)-VBA BEGINNER [英] Copy Cell and Move to Another Cell (Offset) - VBA BEGINNER

问题描述

我有一列有很多空白和条目.我要输入条目(忽略空格)，然后将它们向右移一次并向下移两次，以替换内容.我觉得您会使用偏移功能，但是我不知道如何在VBA中编写此功能.我只使用offset作为公式.任何帮助将不胜感激...

I have a column that has many blanks and entries. I want to take the entries (ignoring the blanks) and move them over to the right once and down twice replacing the contents. I have a feeling you would use the offset function, however I don't know how to write this in VBA. I've only used offset as a formula. Any help would be appreciated...

推荐答案

首先，您需要创建一个循环，循环遍历范围内的所有值.创建循环的方法很多，但这是一个示例:

First you need to create a loop, that moves through all the values of your range. There many ways to create loops, but here is one example:

'find last row of range
lastrow = ActiveSheet.UsedRange.Rows.Count

'Loops through the values from 2 to the last row of range
For x=2 to lastrow 

Next x

然后，我建议遍历该范围，并使用IF函数检查每个单元格值是否符合您的条件:

Then I recommend to loop through the range and check each cell value for your criteria using the IF function:

'Checks for blank value in column A. If not blank  
If Cells(x, 1).Value <> "" then
'Do Something
End IF

现在，要复制新范围内的所有值，只需将旧单元格和新单元格的值设置为相等即可:

Now in order to copy all values in a new range, just set the values of the old and new cell equal:

'Moves value from column A to column B and two cells down
Cells(x+2, 2).Value = Cells(x, 1).Value

总而言之，您的代码如下所示:

In summary your code would look something like this:

Sub MoveValue ()

lastrow = ActiveSheet.UsedRange.Rows.Count

For x=2 to lastrow 
    If Cells(x, 1).Value <> "" then
      Cells(x+2, 2).Value = Cells(x, 1).Value
    End IF
Next x

End Sub

这篇关于复制单元格并移动到另一个单元格(偏移)-VBA BEGINNER的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！